From: Robert Haas <robertmhaas@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: bad selectivity estimates for CASE
Date: Mon, 5 Jan 2009 22:15:29 -0500
Message-ID: <603c8f070901051915p15b3d6a7jbe6f6fdd178143e@mail.gmail.com> (raw)
While looking at a complex query that is being poorly planned by
PostgreSQL 8.2.9, I discovered that any non-trivial CASE...WHEN
expression seems to produce a selectivity estimate of 0.005. This
also happens on HEAD.
psql (8.4devel)
Type "help" for help.
head=# create table tenk (c) as select generate_series(1,10000);
SELECT
head=# alter table tenk alter column c set statistics 100;
ALTER TABLE
head=# analyze tenk;
ANALYZE
head=# explain select * from tenk where c in (1,2,3,4);
QUERY PLAN
------------------------------------------------------
Seq Scan on tenk (cost=0.00..190.00 rows=4 width=4)
Filter: (c = ANY ('{1,2,3,4}'::integer[]))
(2 rows)
head=# explain select * from tenk where case when c in (1,2,3,4) then 1 end = 1;
QUERY PLAN
--------------------------------------------------------------------------------
------------
Seq Scan on tenk (cost=0.00..215.00 rows=50 width=4)
Filter: (CASE WHEN (c = ANY ('{1,2,3,4}'::integer[])) THEN 1 ELSE NULL::integ
er END = 1)
(2 rows)
head=# explain select * from tenk where case when c in (1,2,3,4) then 2 end = 1;
QUERY PLAN
--------------------------------------------------------------------------------
------------
Seq Scan on tenk (cost=0.00..215.00 rows=50 width=4)
Filter: (CASE WHEN (c = ANY ('{1,2,3,4}'::integer[])) THEN 2 ELSE NULL::integ
er END = 1)
(2 rows)
head=# \q
The last example is particularly egregious, since it can never return
true, but the previous example is not much better, since in my actual
query the actual selectivity (against a CASE with multiple WHEN
branches) can be as high as ~0.8, so a value of 0.005 isn't close. It
ends up causing a very expensive nested loop plan when something else
would be better.
Any suggestions would be appreciated.
...Robert
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: robertmhaas@gmail.com
Subject: Re: bad selectivity estimates for CASE
In-Reply-To: <603c8f070901051915p15b3d6a7jbe6f6fdd178143e@mail.gmail.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