Re: indices - used by which user ?

> On Thu, 8 May 2003 09:20:31 +0200 (CEST), <g.hintermayer@inode.at>
> wrote:
>>So that's a factor of about 10 faster, only by changing the user, very
>> strange.
>
> Very, very strange!  Compare the outputs of SHOW ALL for both cases. If
> there are any differences, please inform us.
>
>>         ->  Seq Scan on produkt  (cost=0.00..2417.41 rows=2141
>> width=40)
>                                                ^^^^         ^^^^
>>                                  (actual time=0.02..27.12 rows=2141
>> loops=1)
>
> Unless I'm missing something, your produkt table has more pages than
> tuples.  VACUUM FULL should reduce its size to ca. 22 pages.
>
Could be, I'm running VACUUM only once a week.

Well the whole problem seems to be because of different types of the
joined columns.
I rebuilt my database to have the same datatype on the joined columns
(both character varying(10 now, before one text, one character
varying(10)) and my query works as fast as in the other databases.

Somebody shall correct me if I'm wrong, but that's what I found out:

The optimizer *never* uses an index when doing NATURAL INNER JOIN when the
joined rows have the same data type (at least I could'nt find a case where
he does) regardless if ther's an index on the joined column in one or both
tables or not.
If the joined rows have different datatypes (which should'nt be the normal
case) he sometimes does and sometimes doesn't. The slowdown og these joins
seems to be the type cast (character varying to text) and not the unused
index.

Gerhard