Re: indices - used by which user ?

On Thu, 8 May 2003 g(dot)hintermayer(at)inode(dot)at wrote:

> > On Thu, 8 May 2003 09:20:31 +0200 (CEST), <g(dot)hintermayer(at)inode(dot)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.