From: | Gerhard Hintermayer <g(dot)hintermayer(at)inode(dot)at> |
---|---|
To: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
Cc: | mkoi-pg(at)aon(dot)at, pgsql-general(at)postgresql(dot)org |
Subject: | Re: indices - used by which user ? |
Date: | 2003-05-08 18:21:26 |
Message-ID: | 3EBAA026.8020708@inode.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Stephan Szabo wrote:
> 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.
>
>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arguile | 2003-05-08 18:25:30 | Re: interval function question |
Previous Message | Tom Lane | 2003-05-08 18:00:39 | Re: FETCH ALL positions cursor strangely? |