From: | Dmitry Tkach <dmitry(at)openratings(dot)com> |
---|---|
To: | pg_general <pgsql-general(at)postgresql(dot)org> |
Subject: | Weird query plan |
Date: | 2003-09-17 18:07:27 |
Message-ID: | 3F68A2DF.70109@openratings.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi, everybody!
Here is a weird problem, I ran into...
I have two huge (80 million rows each) tables (a and b), with id as a PK
on both of them and also an FK from b referencing a.
When I try to run a query like:
select * from a, b where a.id >= 7901288 and a.id=b.id limit 1;
The query takes *forever*.
If I do
select * from a,b where b.id >= 7901288 and a.id=b.id limit 1;
then it returns right away.
The query plan looks identical in both cases:
Limit (cost=0.00..12.51 rows=1 width=8)
-> Nested Loop (cost=0.00..1009772807.91 rows=80740598 width=8)
-> Index Scan using b_pkey on b (cost=0.00..375410773.29
rows=80740598 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.85 rows=1 width=4)
... which makes me think that it decides to use b as the outer table
for both cases (which would obviously make it suck in the first one)... :-(
This happens on 7.2.4... I have a 7.3 database with the same schema, but
it is not populated with data, so I could not test it on 7.3...
I looked at the 7.3's query plans though, and they look better to me:
Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using b_pkey on b (cost=0.00..45.50 rows=333
width=4)
Index Cond: (id >= 7901288)
-> Index Scan using a_pkey on a (cost=0.00..4.82 rows=1 width=4)
Index Cond: (a.id = "outer".id)
in the second case, and
Limit (cost=0.00..4.97 rows=1 width=8)
-> Nested Loop (cost=0.00..1657.34 rows=333 width=8)
-> Index Scan using a_pkey on a (cost=0.00..45.50 rows=333
width=4)
Index Cond: (id >= 7901288)
-> Index Scan using b_pkey on b (cost=0.00..4.82 rows=1 width=4)
Index Cond: ("outer".id = b.id)
in the first case... (looks like it does swap them around as I expected)...
Do you know of anything that got fixed between 7.2.4 and 7.3, related to
this problem?
I also noticed that changing a,b to b,a in the from clause doesn't
affect anything... and (what's even more weird) even using an explicit
join doesn't help:
explain select a.duns from a natural join b dm where a.id >= 7901288
limit 1;
NOTICE: QUERY PLAN:
Limit (cost=0.00..12.78 rows=1 width=8)
-> Nested Loop (cost=0.00..1023061272.15 rows=80049919 width=8)
-> Index Scan using b_pkey on b (cost=0.00..380070641.01
rows=81786784 width=4)
-> Index Scan using a_pkey on a (cost=0.00..6.86 rows=1 width=4)
:-(
Any ideas?
Thanks a lot!
Dima
From | Date | Subject | |
---|---|---|---|
Next Message | jason dang | 2003-09-17 18:11:07 | Bug while installing in AIX 5.1 |
Previous Message | Darko Prenosil | 2003-09-17 18:06:40 | Re: psql and blob |