13

Is there a way for me to force a specific join order in Postgres?

I've got a query that looks like this. I've eliminated a bunch of stuff that was in the real query, but this simplification demonstrates the issue. What's left shouldn't be too cryptic: Using a role/task security system, I'm trying to determine whether a given user has privileges to perform a given task.

select task.taskid
from userlogin
join userrole using (userloginid)
join roletask using (roleid)
join task using (taskid)
where loginname='foobar'
and taskfunction='plugh'

But I realized that the program already knows the value of userlogin, so it seemed the query could be made more efficient by skipping the lookup on userlogin and just filling in the userloginid, like this:

select task.taskid
from userrole
join roletask using (roleid)
join task using (taskid)
where userloginid=42
and taskfunction='plugh'

When I did that -- eliminating a table from the query and hard-coding the value retrieved from that table instead -- the explain plan time went up! In the original query, Postgres read userlogin then userrole then roletask then task. But in the new query, it decided to read roletask first, and then join to userrole, even though this required doing a full-file scan on roletask.

Full explain plans are:

Version 1:

Hash Join  (cost=12.79..140.82 rows=1 width=8) 
  Hash Cond: (roletask.taskid = task.taskid) 
  ->  Nested Loop  (cost=4.51..129.73 rows=748 width=8) 
        ->  Nested Loop  (cost=4.51..101.09 rows=12 width=8) 
              ->  Index Scan using idx_userlogin_loginname on userlogin  (cost=0.00..8.27 rows=1 width=8) 
                    Index Cond: ((loginname)::text = 'foobar'::text) 
              ->  Bitmap Heap Scan on userrole  (cost=4.51..92.41 rows=33 width=16) 
                    Recheck Cond: (userrole.userloginid = userlogin.userloginid) 
                    ->  Bitmap Index Scan on idx_userrole_login  (cost=0.00..4.50 rows=33 width=0) 
                          Index Cond: (userrole.userloginid = userlogin.userloginid) 
        ->  Index Scan using idx_roletask_role on roletask  (cost=0.00..1.50 rows=71 width=16) 
              Index Cond: (roletask.roleid = userrole.roleid) 
  ->  Hash  (cost=8.27..8.27 rows=1 width=8) 
        ->  Index Scan using idx_task_taskfunction on task  (cost=0.00..8.27 rows=1 width=8) 
              Index Cond: ((taskfunction)::text = 'plugh'::text)

Version 2:

Hash Join  (cost=96.58..192.82 rows=4 width=8) 
  Hash Cond: (roletask.roleid = userrole.roleid) 
  ->  Hash Join  (cost=8.28..104.10 rows=9 width=16) 
        Hash Cond: (roletask.taskid = task.taskid) 
        ->  Seq Scan on roletask  (cost=0.00..78.35 rows=4635 width=16) 
        ->  Hash  (cost=8.27..8.27 rows=1 width=8) 
              ->  Index Scan using idx_task_taskfunction on task  (cost=0.00..8.27 rows=1 width=8) 
                    Index Cond: ((taskfunction)::text = 'plugh'::text) 
  ->  Hash  (cost=87.92..87.92 rows=31 width=8) 
        ->  Bitmap Heap Scan on userrole  (cost=4.49..87.92 rows=31 width=8) 
              Recheck Cond: (userloginid = 42) 
              ->  Bitmap Index Scan on idx_userrole_login  (cost=0.00..4.49 rows=31 width=0) 
                    Index Cond: (userloginid = 42)

(Yes, I know that in both cases the costs are low and the difference doesn't look like it would matter. But this is after I eliminated a bunch of additional work from the query to simplify what I have to post. The real query still isn't outrageous, but I'm more interested in the principle.)

Improve this question
4
  • 3
    Can you show the query plans (explain analyze) and table definitions? Commented Sep 23, 2009 at 20:37
  • 1
    Okay, you asked, I replaced the hypothetical simple example with the real query, and added the explain plan results. Oh, I'm sure that I could add some additional indexes to speed up the second query, but that's not the point. Why did Postgres choose a plan that was less than the best that it could do, given the queries that it had? Especially when it demonstrated that it could do better if I made the query more complicated? Commented Sep 25, 2009 at 13:50
  • 1
    are you comparing the actual run times of the queries or just looking at the headline cost in the explain plan? the costs? notably, you've just posted explain output, not explain analyze. although you'd expect a higher cost to equate to a slower query run, it might not work out that way. Commented Sep 26, 2009 at 11:05
  • 1
    True, I have not compared actual run-times, and you are of course correct that the relative speeds might reverse. But I think that would be beside the point: Postgres would have to base its plan decisions on calculated plan costs, not actual costs, and by its own standards, its chosen an inferior plan. If the actual run time turns out to be better, that's just dumb luck. Commented Sep 28, 2009 at 13:34

2 Answers 2

Reset to default
9

This page in the documentation describes how to prevent the PostgreSQL optimizer from reordering joined tables, allowing you to control the order of joins yourself:

http://www.postgresql.org/docs/current/interactive/explicit-joins.html

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

PostgreSQL really does have the best documentation I've seen of an RDBMS.
Have you tried this? I certainly don't want to change this setting for all queries, just for one or two here and there. If I change it with a set statement, does that affect the entire database engine, or just the current connection, or the current transaction? Hmm, I suppose I could test this by opening two connections, setting it from one, and then seeing if explain plans on the other change ...
I haven't tried it. You're right that trying it yourself in a pair of concurrent sessions is the best way to be sure. Doc might be wrong (though as @hgiminez points out, rarely so in PostgreSQL's doc).
I'll give you the points for what is apparently the textbook correct answer. It's just not the answer I wanted to hear! I wanted some way to do hints, like in Oracle.
2

Are you sure your table statistics are up to date? When PostgreSQLs cost based optimizer fails with such trivial things it's a pretty good sign something is seriously wrong with the table statistics. It's better to fix the root cause than to work around it by overriding the built in optimizer because the problem will inevitably pop up somewhere else as well.

Run ANALYZE on the affected tables and see if it makes PostgreSQL pick a different plan. If it still chooses something silly it would be really interesting to see the query plans. The optimizer not doing the right thing is usually considered a bug.

Improve this answer

2 Comments

Yes. After my initial surprising results, I re-ran the analyze on those tables and then re-did the explain plans, and the results were similar.
That does seem a bit strange. What is your effective_cache_size setting? The default 128M could lead to unreasonable sequential scans on small to medium tables. Also, for heavily cached databases lowering random_page_cost might be a good idea.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.