This week's book giveaway is in the Cloud/Virtualizaton forum.
We're giving away four copies of Mesos in Action and have Roger Ignazio on-line!
See this thread for details.
Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

general db question

 
howie shannon
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
Scott Johnson
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
[Howie]Which is the outer relation

One is the outer relation.

[Howie]i believe using the smaller table as the inner speeds up the join

Maybe. Maybe not. I think that this is indeterminate based on the information provided. You'll need to try both to find out.

The number of rows that match the where conditions is probably more significant than the table sizes in determining which table should be the inner and which should be outer.

[Howie]i am forcing postgres to use nestedloop.

That's probably a dangerous thing to do. The database optimizer is usually a better judge of how to run the query. Supposing that you are able to choose a better path than the optimizer right now, your choice may become sub-optimal over time as the data changes and/or the optimizer changes after an upgrade. You could actually start forcing a very non-optimal path. Except in the most extreme cases, I would let the optimizer do its job.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic