Re: indices - used by which user ?

Stephan Szabo wrote:
> On Thu, 8 May 2003 g.hintermayer@inode.at wrote:
>
>
>>>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.
>
>
> I can get it to in at least some circumstances:
> create table t1(a int unique);
> create table t2(a int unique);
> explain select * from t1 natural inner join t2;
>
> Also, I didn't see an index on produkt.p_code which may or may not help in
> general.
>
> I'd have said if possible to try with enable_seqscan=off, but if there's
> no index on the other I'm not sure the numbers would be meaningful.
>
>

You're right. When the joined columns have the same type, the index has
to be in produkt.p_code, then it is used. I have been misled, that in my
posted example the joined column had different types and the index on
auftrag_l?.p_code was used sometimes.
But interesting that the optimizer sometimes uses the index and
sometimes does'nt in that case. And that he uses the index of the other
table (auftrag_l?) and not of produkt.

Thanks for your help

Gerhard