bad selectivity estimates for CASE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux