To: josh@agliodbs.com
Cc: Guillaume Smet <guillaume_ml@smet.org>,
	pgsql-performance@postgresql.org
Subject: Re: Bad plan after vacuum analyze 
In-reply-to: <200505111110.15983.josh@agliodbs.com> 
References: <42823F94.8050009@smet.org> <200505111110.15983.josh@agliodbs.com>
Comments: In-reply-to Josh Berkus <josh@agliodbs.com>
	message dated "Wed, 11 May 2005 11:10:15 -0700"
Date: Wed, 11 May 2005 14:58:46 -0400
Message-ID: <13964.1115837926@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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