From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Alistair Bayley <alistair@abayley.org>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Why is the optimiser choosing the slower query, or, understanding explain analyze output
Date: Mon, 17 Feb 2014 20:40:10 -0500
Message-ID: <11385.1392687610@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMkU=1x16-S5V+BKD0Pr2K3NyCQvv7hsCP1Fk4UcwHecssV_KQ@mail.gmail.com>
References: <CAKYODbj6xQDShQd2g3+9oue0+cz3NXjrnRvmbrfRDRoReWxFKQ@mail.gmail.com>
<CAMkU=1x16-S5V+BKD0Pr2K3NyCQvv7hsCP1Fk4UcwHecssV_KQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Jeff Janes <jeff.janes@gmail.com> writes:
> On Mon, Feb 17, 2014 at 1:54 PM, Alistair Bayley <alistair@abayley.org>wrote:
>> I want to understand why the optimiser is choosing the plan with
>> sequential table scans, rather than the plan with index scans.
> The planner clamps the estimated number of rows from an index scan at 1
> row, even if it actually believes the number will be 0. That makes the
> logical simpler, avoiding needs to test for division by zero all over the
> place, and probably makes it more robust to mis-estimation in most use
> cases. But in this case, that means it thinks it will find 34 rows, one
> from each partition, which is way too high.
Even if it believed the zero row estimate it's probably getting
internally, the cost estimate wouldn't change much, because as you say
it's still got to assume that the index will be traversed to verify that
there's no such row(s).
I notice though that the cost estimate for the seqscan plan isn't all that
much lower than that for the indexscan plan. Probably lowering
random_page_cost a bit would change the planner's mind. We have no
information about total size of database vs available RAM, but if it's
a mostly memory-resident database then such a change would be a good idea.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, jeff.janes@gmail.com, alistair@abayley.org
Subject: Re: Why is the optimiser choosing the slower query, or, understanding explain analyze output
In-Reply-To: <11385.1392687610@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