From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Bad cost estimate with FALSE filter condition
Date: Mon, 16 Mar 2015 10:15:53 -0700
Message-ID: <55070FC9.4010307@agliodbs.com> (raw)
In-Reply-To: <WM!4d936314dc9313889ef2e5e6a4381fd285d187be51c5b5de339e449728769003a8062a9992b607935e468812a6b9b787!@asav-3.01.com>
References: <54F119A6.9080106@agliodbs.com>
<WM!4d936314dc9313889ef2e5e6a4381fd285d187be51c5b5de339e449728769003a8062a9992b607935e468812a6b9b787!@asav-3.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
So ... should I assume my diagnosis is correct? Haven't heard any other
suggestions.
On 02/27/2015 05:28 PM, Josh Berkus wrote:
> All:
>
> This got posted to pgsql-bugs, but got no attention there[1], so I'm
> sending it to this list.
>
> Test case:
>
> createdb bench
> pgbench -i -s bench
> \c bench
>
> bench=# explain select * from pgbench_accounts where aid = 2;
> QUERY PLAN
> ---------------------------------------------------------------
> Index Scan using pgbench_accounts_pkey on pgbench_accounts
> (cost=0.42..8.44 rows=1 width=97)
> Index Cond: (aid = 2)
> (2 rows)
>
> bench=# explain select * from pgbench_accounts where aid = 2 and false;
>
> QUERY PLAN
> -------------------------------------------------
> Result (cost=0.00..26394.00 rows=1 width=97)
> One-Time Filter: false
> -> Seq Scan on pgbench_accounts (cost=0.00..26394.00 rows=1 width=97)
> (3 rows)
>
> This seems like a special case of the "aborted plan cost", that is, when
> the planner expects to abort a plan early, it nevertheless returns the
> full cost for the non-aborted version of the query, rather than the
> working cost, which is based on the abort.
>
> For example:
>
> bench=# create index on pgbench_accounts(bid);
> CREATE INDEX
> bench=# explain select * from pgbench_accounts where bid = 2;
> QUERY PLAN
>
> ----------------------------------------------------------------------------------------------------------
> Index Scan using pgbench_accounts_bid_idx on pgbench_accounts
> (cost=0.42..4612.10 rows=102667 width=97)
> Index Cond: (bid = 2)
> (2 rows)
>
> bench=# explain select * from pgbench_accounts where bid = 2 limit 1;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> Limit (cost=0.00..0.28 rows=1 width=97)
> -> Seq Scan on pgbench_accounts (cost=0.00..28894.00 rows=102667
> width=97)
> Filter: (bid = 2)
> (3 rows)
>
> So in this case, the top-level node returns a lower cost because the
> planner knows that it will find a row with bid=2 fairly quickly in the
> seq scan. But in the WHERE FALSE example, that scan *is* the top-level
> node, so the planner returns a fictitious cost for the whole query.
>
> Or is there something else at work here?
>
> [1]
> http://www.postgresql.org/message-id/20150225194953.2546.86836@wrigleys.postgresql.org
>
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
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: josh@agliodbs.com
Subject: Re: Bad cost estimate with FALSE filter condition
In-Reply-To: <55070FC9.4010307@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