File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes general db question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "general db question" Watch "general db question" New topic

general db question

howie shannon

Joined: Nov 21, 2006
Posts: 1
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 = AND>3000 AND>1000;
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 = AND ( > 1000))
Total runtime: 129.435 ms
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
[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.
I agree. Here's the link:
subject: general db question
It's not a secret anymore!