From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Missed index opportunity for outer join?
Date: Tue, 06 Dec 2005 15:33:31 -0500
Message-ID: <15962.1133901211@sss.pgh.pa.us> (raw)
In-Reply-To: <4395F1F3.1000803@cheapcomplexdevices.com>
References: <Pine.LNX.4.58.0512051401090.21335@greenie.cheapcomplexdevices.com>
<3267.1133822299@sss.pgh.pa.us>
<Pine.LNX.4.58.0512051443130.21474@greenie.cheapcomplexdevices.com>
<4678.1133888239@sss.pgh.pa.us>
<4395F1F3.1000803@cheapcomplexdevices.com>
Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
> The "point_features" table is pretty dynamic and it's possible
> that the data changed between my 'explain analyze' statement in
> the first post in this thread. However since both of them
> show an estimate of "rows=948" and returned an actual of 917 I
> don't think that happened.
Yeah, I had considered the same explanation and rejected it for the same
reason. Also, the difference in estimated cost is significant (265.85
for the seqscan vs 172.17 for the bitmap scan) so it's hard to think
that a small change in stats --- so small as to not reflect in estimated
row count --- would change the estimate by that much.
[ thinks some more... ] Of course, what we have to remember is that the
planner is actually going to choose based on the ultimate join cost, not
on the subplan costs. The reason the seqscan survived initial
comparisons at all is that it has a cheaper startup cost (less time to
return the first tuple) than the bitmap scan, and this will be reflected
into a cheaper startup cost for the overall nestloop. The extra hundred
units of total cost would only reflect into the nestloop total cost ---
and there, they would be considered "down in the noise" compared to a
90k total estimate. So probably what happened is that the planner
preferred this plan on the basis that the total costs are the same to
within estimation error while the startup cost is definitely less.
In this explanation, the reason for the change in plans over time could
be a change in the statistics for the other table. Is "facets" more
dynamic than "point_features"?
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, rm_pg@cheapcomplexdevices.com
Subject: Re: Missed index opportunity for outer join?
In-Reply-To: <15962.1133901211@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