From: Josh Berkus <josh@agliodbs.com>
To: Guillaume Smet <guillaume_ml@smet.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad plan after vacuum analyze
Date: Wed, 11 May 2005 11:10:15 -0700
Message-ID: <200505111110.15983.josh@agliodbs.com> (raw)
In-Reply-To: <42823F94.8050009@smet.org>
References: <42823F94.8050009@smet.org>
Guillaume,
> We reproduced the problem on a 7.4.5 and on a 7.4.7 server.
> * we load the dump in a new database
> * query: it's fast (< 1ms)
> * VACUUM FULL ANALYZE;
> * query: it's really slow (130ms) and it's another plan
> * set enable_seqscan=off;
> * query: it's fast (< 1ms) : it uses the best plan
Looking at this, the planner seems convinced that the merge join is the
easiest way to do the OUTER JOINS, but it appears to be wrong; a nested loop
is faster.
This isn't the only place I've encountered our optimizer doing this --
underestimating the cost of a merge join. This seems to be becuase the
merge_join vs. nested_loop decision seems to be being made in the planner
without taking the double-sort and index access costs into account. This
query is an excellent example:
"good" plan:
Nested Loop Left Join (cost=2.44..17.36 rows=1 width=5532) (actual
time=0.441..0.466 rows=1 loops=1)
Join Filter: ("outer".parent_application_id = "inner".application_id)
-> Nested Loop Left Join (cost=2.44..15.73 rows=1 width=5214) (actual
time=0.378..0.402 rows=1 loops=1)
See, here the planner thinks that the 2 nested loops will cost "35".
"bad" plan:
Merge Right Join (cost=9.27..9.48 rows=1 width=545) (actual
time=129.364..129.365 rows=1 loops=1)
Merge Cond: ("outer".application_id = "inner".parent_application_id)
-> Index Scan using applicati_applicati_id_p_ogstm on applications t116
(cost=0.00..5.51 rows=28 width=20) (actual time=0.030..0.073 rows=28 loops=1)
-> Sort (cost=9.27..9.27 rows=1 width=529) (actual time=129.202..129.203
rows=1 loops=1)
Sort Key: t22.parent_application_id
-> Merge Right Join (cost=8.92..9.26 rows=1 width=529) (actual
time=129.100..129.103 rows=1 loops=1)
Merge Cond: ("outer".object_id = "inner".parent_application_id)
-> Index Scan using acs_objects_object_id_p_hhkb1 on
acs_objects t98 (cost=0.00..2554.07 rows=33510 width=81) (actual
time=0.043..56.392 rows=33510 loops=1)
-> Sort (cost=8.92..8.93 rows=1 width=452) (actual
time=0.309..0.310 rows=1 loops=1)
Sort Key: t22.parent_application_id
Here the planner chooses a merge right join. This decision seems to have been
made entirely on the basis of the cost of the join itself (total of 17)
without taking the cost of the sort and index access (total of 2600+) into
account.
Tom, is this a possible error in planner logic?
--
--Josh
Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com, guillaume_ml@smet.org
Subject: Re: Bad plan after vacuum analyze
In-Reply-To: <200505111110.15983.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox