Обсуждение: BUG #13827: planner chooses more expensive plan than it should
The following bug has been logged on the website:
Bug reference: 13827
Logged by: Michal Schwarz
Email address: michal.schwarz@gmail.com
PostgreSQL version: 9.2.14
Operating system: CentOS release 6.5 (Final), x86_64
Description:
Correct behaviour:
=> explain select 1 FROM nakupy_prodeje n, smlouvy s WHERE
n.smlouva_id=s.smlouva_id AND s.osoba_id='900316';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..18415.76 rows=88 width=0)
-> Index Scan using smlouvy_osoba_id on smlouvy s (cost=0.00..678.38
rows=170 width=4)
Index Cond: (osoba_id = 900316)
-> Index Only Scan using nakupy_prodeje_smlouva_id on nakupy_prodeje n
(cost=0.00..100.99 rows=335 width=4)
Index Cond: (smlouva_id = s.smlouva_id)
(5 rows)
Total expected cost is 18415 and 88 rows. OK.
Problematic behaviour is when I just add "AND n.datum_realizace is null" to
original WHERE condition. This query should be at least as fast as previous
query, because everything is absolutely the same, and only an ADDITIONAL
condition "AND n.datum_realizace is null" was used.
But PostgreSQL chooses much expensive query plan this time, even when it
could just use plan similar to previous with only additional filtering
applied to original 88 expected rows:
=> explain select 1 FROM nakupy_prodeje n, smlouvy s WHERE
n.smlouva_id=s.smlouva_id AND s.osoba_id='900316' AND n.datum_realizace is
null;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=680.51..42187.98 rows=3 width=0)
Hash Cond: (n.smlouva_id = s.smlouva_id)
-> Index Scan using nakupy_prodeje_datum_realizace on nakupy_prodeje n
(cost=0.00..40380.37 rows=300551 width=4)
Index Cond: (datum_realizace IS NULL)
-> Hash (cost=678.38..678.38 rows=170 width=4)
-> Index Scan using smlouvy_osoba_id on smlouvy s
(cost=0.00..678.38 rows=170 width=4)
Index Cond: (osoba_id = 900316)
(7 rows)
Why did PG choose a plan with cost=41287, when it could use much simpler
plan based on previous example?
Or, more specifically, why is PG using Index Cond for "datum_realizace IS
NULL" with 300000 resulting expected rows, when it could apparently use
simpler plan.
For me, it is like a difference between optimal:
"how to count all people in room?" => "let's look at them ... yes, there
are 5 people here"
and extremely sub-optimal:
"how to count all people WITH BEARD in room?" => "let's count ALL THE
PEOPLE IN THE TOWN (there will be approx. 300000 of them), then find out all
WITH BEARD, and finally look whether any of them is in this room".
:-)
Table structures are:
=> \d nakupy_prodeje
Table
"public.nakupy_prodeje"
Column | Type |
Modifiers
---------------------------+-----------------------------+----------------------------------------------------------------------------
prikaz_id | integer | not null default
nextval(('nakupy_prodeje_prikaz_id_seq'::text)::regclass)
smlouva_id | integer | not null
datum_realizace | timestamp without time zone |
Indexes:
"nakupy_prodeje_pkey" PRIMARY KEY, btree (prikaz_id)
"nakupy_prodeje_datum_realizace" btree (datum_realizace)
"nakupy_prodeje_smlouva_id" btree (smlouva_id)
=> \d smlouvy
Table
"public.smlouvy"
Column | Type |
Modifiers
---------------------------------------+-----------------------------+----------------------------------------------------------------------
smlouva_id | integer | not
null default nextval(('smlouvy_smlouva_id_seq'::text)::regclass)
osoba_id | integer | not
null
Indexes:
"smlouvy_pkey" PRIMARY KEY, btree (smlouva_id)
"smlouvy_osoba_id" btree (osoba_id)
And ANALYZE was run on these tables.
Hi
2015-12-18 15:33 GMT+01:00 <michal.schwarz@gmail.com>:
> The following bug has been logged on the website:
>
> Bug reference: 13827
> Logged by: Michal Schwarz
> Email address: michal.schwarz@gmail.com
> PostgreSQL version: 9.2.14
> Operating system: CentOS release 6.5 (Final), x86_64
> Description:
>
> Correct behaviour:
>
> => explain select 1 FROM nakupy_prodeje n, smlouvy s WHERE
> n.smlouva_id=s.smlouva_id AND s.osoba_id='900316';
> QUERY PLAN
>
> -----------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..18415.76 rows=88 width=0)
> -> Index Scan using smlouvy_osoba_id on smlouvy s (cost=0.00..678.38
> rows=170 width=4)
> Index Cond: (osoba_id = 900316)
> -> Index Only Scan using nakupy_prodeje_smlouva_id on nakupy_prodeje n
> (cost=0.00..100.99 rows=335 width=4)
> Index Cond: (smlouva_id = s.smlouva_id)
> (5 rows)
>
> Total expected cost is 18415 and 88 rows. OK.
>
> Problematic behaviour is when I just add "AND n.datum_realizace is null" to
> original WHERE condition. This query should be at least as fast as previous
> query, because everything is absolutely the same, and only an ADDITIONAL
> condition "AND n.datum_realizace is null" was used.
>
> But PostgreSQL chooses much expensive query plan this time, even when it
> could just use plan similar to previous with only additional filtering
> applied to original 88 expected rows:
>
> => explain select 1 FROM nakupy_prodeje n, smlouvy s WHERE
> n.smlouva_id=s.smlouva_id AND s.osoba_id='900316' AND n.datum_realizace is
> null;
> QUERY PLAN
>
>
----------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=680.51..42187.98 rows=3 width=0)
> Hash Cond: (n.smlouva_id = s.smlouva_id)
> -> Index Scan using nakupy_prodeje_datum_realizace on nakupy_prodeje n
> (cost=0.00..40380.37 rows=300551 width=4)
> Index Cond: (datum_realizace IS NULL)
> -> Hash (cost=678.38..678.38 rows=170 width=4)
> -> Index Scan using smlouvy_osoba_id on smlouvy s
> (cost=0.00..678.38 rows=170 width=4)
> Index Cond: (osoba_id = 900316)
> (7 rows)
>
please, send output of EXPLAIN ANALYZE
Regards
Pavel
>
> Why did PG choose a plan with cost=41287, when it could use much simpler
> plan based on previous example?
>
> Or, more specifically, why is PG using Index Cond for "datum_realizace IS
> NULL" with 300000 resulting expected rows, when it could apparently use
> simpler plan.
>
> For me, it is like a difference between optimal:
> "how to count all people in room?" => "let's look at them ... yes, there
> are 5 people here"
> and extremely sub-optimal:
> "how to count all people WITH BEARD in room?" => "let's count ALL THE
> PEOPLE IN THE TOWN (there will be approx. 300000 of them), then find out
> all
> WITH BEARD, and finally look whether any of them is in this room".
> :-)
>
> Table structures are:
>
> => \d nakupy_prodeje
> Table
> "public.nakupy_prodeje"
> Column | Type |
> Modifiers
>
>
---------------------------+-----------------------------+----------------------------------------------------------------------------
> prikaz_id | integer | not null default
> nextval(('nakupy_prodeje_prikaz_id_seq'::text)::regclass)
> smlouva_id | integer | not null
> datum_realizace | timestamp without time zone |
> Indexes:
> "nakupy_prodeje_pkey" PRIMARY KEY, btree (prikaz_id)
> "nakupy_prodeje_datum_realizace" btree (datum_realizace)
> "nakupy_prodeje_smlouva_id" btree (smlouva_id)
>
> => \d smlouvy
> Table
> "public.smlouvy"
> Column | Type |
> Modifiers
>
>
---------------------------------------+-----------------------------+----------------------------------------------------------------------
> smlouva_id | integer | not
> null default nextval(('smlouvy_smlouva_id_seq'::text)::regclass)
> osoba_id | integer | not
> null
> Indexes:
> "smlouvy_pkey" PRIMARY KEY, btree (smlouva_id)
> "smlouvy_osoba_id" btree (osoba_id)
>
>
> And ANALYZE was run on these tables.
>
>
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
michal.schwarz@gmail.com writes:
> => explain select 1 FROM nakupy_prodeje n, smlouvy s WHERE
> n.smlouva_id=s.smlouva_id AND s.osoba_id='900316';
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..18415.76 rows=88 width=0)
> -> Index Scan using smlouvy_osoba_id on smlouvy s (cost=0.00..678.38
> rows=170 width=4)
> Index Cond: (osoba_id = 900316)
> -> Index Only Scan using nakupy_prodeje_smlouva_id on nakupy_prodeje n
> (cost=0.00..100.99 rows=335 width=4)
> Index Cond: (smlouva_id = s.smlouva_id)
> (5 rows)
> Total expected cost is 18415 and 88 rows. OK.
> Problematic behaviour is when I just add "AND n.datum_realizace is null" to
> original WHERE condition. This query should be at least as fast as previous
> query, because everything is absolutely the same, and only an ADDITIONAL
> condition "AND n.datum_realizace is null" was used.
But datum_realizace is not in the nakupy_prodeje_smlouva_id index. So it
could not have done this "exactly the same"; it would have had to have
used a regular index scan, which is a whole lot more expensive than the
index-only scan because it involves fetching heap tuples too. Evidently
the planner thinks the hash join is a better option than that.
If you try turning off enable_hashjoin and enable_mergejoin, you'll
probably get the nestloop/indexscan plan, and you'll be able to see
what cost the planner is assigning to it; but it will certainly be
higher than for the plan that got selected.
regards, tom lane