Thread: Query plan different depending on the value of where filter
Hi All,
I have an interesting query scenario I’m trying to understand.
I came across this while investigating a slow query in our application.
I’ve been able to reproduce the scenario in a psql script that sets up the tables and runs the queries.
Script here http://pastebin.com/CBkdDmWp if anyone is interested.
This is the scenario.
Version is "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 32-bit"
Three tables
Outer_tab : 5000 rows
Inner_tab_1 : 1000 rows
Inner_tab_2 : 16000 rows
This is the query
SELECT outer_tab.outer_key
FROM outer_tab
WHERE outer_tab.outer_key IN (
SELECT inner_tab_1.key_to
FROM inner_tab_2
INNER JOIN inner_tab_1 ON (inner_tab_2.outer_key = inner_tab_1.key_from AND inner_tab_1.type = 2)
WHERE outer_tab.outer_key = inner_tab_1.key_to AND inner_tab_2.group_id = 9
);
Two important things here, type = 2 does not occur in inner_tab_1 and group_id = 9 does not occur in inner_tab_2 and group_id is not indexed.
The result is 0 rows.
Now this is quite slow about 15 seconds on my machine.
Here is the explain plan http://explain.depesz.com/s/BVg
I understand that the seq scan on inner_tab_2 and its 16000 rows is the culprit and the easy fix is to swap inner_tab_2 and inner_tab_1 between the FROM and the JOIN.
This lets it drive off inner_tab_1 with an index scan and skip the sequential scan as seen here http://explain.depesz.com/s/pkG
Much better at 14ms.
That’s fine but what has me somewhat confused is if group_id in the WHERE is changed to 1, which does exist in inner_tab_2, we get quite a different plan.
http://explain.depesz.com/s/FX4
It’s quick too 63ms
What I don’t understand is why the plan is different just because the group_id = has changed value?
Does the planner have some statistical info on the contents of non-indexed rows?
I don’t quite understand why this plan executes the sequential scan once, whereas the slow one does it 5001 times, which I believe is the main source of the difference.
Also if I don’t ANALYZE the tables the original query will run in a few ms instead of 15 seconds, it actually uses the same query plan that swapping the tables creates.
So it runs the index scan on inner_tab_1 first.
It’s a bit surprising that with ANALYSE it picks a plan that is so much worse.
Any one able to shed some light?
Thanks for your time,
Denis Looby
"Looby, Denis" <denis.looby@hp.com> writes: > What I don't understand is why the plan is different just because the group_id = has changed value? > Does the planner have some statistical info on the contents of non-indexed rows? Of course. In this case it knows that a nestloop would be a loser because many rows would be fetched from the outer table. > I don't quite understand why this plan executes the sequential scan once, whereas the slow one does it 5001 times, whichI believe is the main source of the difference. In the hash join case, it builds the hash table, discovers that it's empty (because there are no rows with type = 2), and concludes that it need not scan the outer relation. This is a corner case that the planner doesn't try to account for because it couldn't be predicted reliably. I wouldn't get too excited about it if I were you, because presumably the case isn't going to happen all that much in production either (else why are you bothering with the query?). Personally I'd suggest getting rid of the WHERE outer_tab.outer_key = inner_tab_1.key_to clause, which is entirely redundant with the IN condition and is preventing the planner from seeing that it could optimize the IN into a semijoin. regards, tom lane