From: Tom Lane <tgl@sss.pgh.pa.us>
To: josh@agliodbs.com
Cc: Guillaume Smet <guillaume_ml@smet.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Bad plan after vacuum analyze
Date: Wed, 11 May 2005 14:58:46 -0400
Message-ID: <13964.1115837926@sss.pgh.pa.us> (raw)
In-Reply-To: <200505111110.15983.josh@agliodbs.com>
References: <42823F94.8050009@smet.org>
<200505111110.15983.josh@agliodbs.com>
Josh Berkus <josh@agliodbs.com> writes:
> -> 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?
No, it certainly hasn't forgotten to add in the costs of the inputs.
There might be a bug here, but if so it's much more subtle than that.
It looks to me like the planner believes that the one value of
t22.parent_application_id joins to something very early in the
acs_objects_object_id_p_hhkb1 sort order, and that it will therefore not
be necessary to run the indexscan to completion (or indeed very far at
all, considering that it's including such a small fraction of the total
indexscan cost).
andrew@supernews pointed out recently that this effect doesn't apply to
the outer side of an outer join; releases before 7.4.8 mistakenly think
it does. But unless my wires are totally crossed today, acs_objects is
the nullable side here and so that error isn't applicable anyway.
So, the usual questions: have these two tables been ANALYZEd lately?
If so, can we see the pg_stats rows for the object_id and
parent_application_id columns?
regards, tom lane
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: tgl@sss.pgh.pa.us, josh@agliodbs.com, guillaume_ml@smet.org
Subject: Re: Bad plan after vacuum analyze
In-Reply-To: <13964.1115837926@sss.pgh.pa.us>
* 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