• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

general db question

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Ranch Hand
Posts: 518
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic