Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id AAB976321D9 for ; Mon, 5 Jan 2009 23:16:12 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 47860-06-5 for ; Mon, 5 Jan 2009 23:16:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f19.google.com (mail-bw0-f19.google.com [209.85.218.19]) by mail.postgresql.org (Postfix) with ESMTP id D8AFF6321D8 for ; Mon, 5 Jan 2009 23:15:32 -0400 (AST) Received: by bwz12 with SMTP id 12so20860338bwz.19 for ; Mon, 05 Jan 2009 19:15:29 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:mime-version:content-type:content-transfer-encoding :content-disposition; bh=oi9Fkl+Iv6o1Ua1mw9MacghDRFINQCyDwTFKRTKifzs=; b=lcBo/j0trFyoCRLvS/nhVf4lNRPQyN+1T1qe2yNUFqKlZgV9JAzK7QGYRySwDqNjl/ sd4sYhNQEa6LQMLtXo4+WRK2fPyy87nufwJprasafucKDkUnOPdT169u8maBRECav2Xh sGsL/DKcp1Oy6volJ3LpkPVpCk1lFgHdIZ/MY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:mime-version:content-type :content-transfer-encoding:content-disposition; b=uhL3WWyuHoDshF+HETo5eaU9ORX/8989Xp+fbkMrGI/bBi1HJU0djZyexOBZJaIr9K 3UbXGnm+wXF9fFwZVYm5buHIfvKLX5xyGwNfOXY2+WcqV0VrD52kd14g9XgYPgd5BlK7 4j88DGaOjF7Cyu6PEHKwONSCToZyohxEaFiRM= Received: by 10.223.108.210 with SMTP id g18mr15114841fap.38.1231211729669; Mon, 05 Jan 2009 19:15:29 -0800 (PST) Received: by 10.223.107.134 with HTTP; Mon, 5 Jan 2009 19:15:29 -0800 (PST) Message-ID: <603c8f070901051915p15b3d6a7jbe6f6fdd178143e@mail.gmail.com> Date: Mon, 5 Jan 2009 22:15:29 -0500 From: "Robert Haas" To: "pgsql-performance@postgresql.org" Subject: bad selectivity estimates for CASE MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200901/7 X-Sequence-Number: 32286 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