posted 17 years ago
hi all
I did 'explained analyze'ed in postgresql. But I couldn't understand the output. Which is the outer relation and which is the inner relation? Is two the outer relation? If so, does it that the optimizer didn't choose the smaller table as the inner relation(i believe using the smaller table as the inner speeds up the join, at least for nestedloop join).
BTW, i am forcing postgres to use nestedloop.
explain analyze SELECT COUNT(*) FROM One, Two WHERE One.id = Two.id AND one.id>3000 AND two.id>1000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21439.10..21439.10 rows=1 width=0) (actual time=129.368..129.369 rows=1 loops=1)
-> Nested Loop (cost=0.00..21423.25 rows=6339 width=0) (actual time=0.039..119.071 rows=7000 loops=1)
-> Index Scan using one_id on one (cost=0.00..154.73 rows=7017 width=4) (actual time=0.022..11.787 rows=7000 loops=1)
Index Cond: (id > 3000)
-> Index Scan using two_id on two (cost=0.00..3.02 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=7000)
Index Cond: (("outer".id = two.id) AND (two.id > 1000))
Total runtime: 129.435 ms