Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 47C52632281
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  6 Jan 2009 08:25:45 -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 43753-09
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  6 Jan 2009 08:25:35 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from fk-out-0910.google.com (fk-out-0910.google.com
 [209.85.128.184])
	by mail.postgresql.org (Postfix) with ESMTP id 2262463227F
	for <pgsql-performance@postgresql.org>;
	Tue,  6 Jan 2009 08:25:34 -0400 (AST)
Received: by fk-out-0910.google.com with SMTP id e30so4350714fke.3
	for <pgsql-performance@postgresql.org>;
	Tue, 06 Jan 2009 04:25: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:cc:in-reply-to:mime-version:content-type
	:content-transfer-encoding:content-disposition:references;
	bh=LW10+7l4iOYvHl70RE/LIP/r5Puq+qF4blcl4jBckKA=;
	b=V7W4K+9LcsL5UtgB4RdBa5kxRvNGCJiK65H/rI8iC2TsVkvLgf4e13VQZqnlOdg/bw
	Li92NoWl06I93SuzOQWZQCvflTdI8rDIHS9BYvpb3n3Au9lJVU5vuxMpytf+nzILe/aQ
	vdM67Mpb0aR+6flYLjYu0Pn/fk+FVj2Tmt5Ho=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=u7j83d0VOyRoCoBZpP1awKYANQNXVd9maTRkRr5ogLwLyl5SLofH6ZuU5Knv+atsP4
	Oxwtndx6ARRRuNU7xjWbN4TqNuvQ5B12vJbi28uJpeFC+Ilczo1TJTHFFkHUMi76aR2/
	NAwjX8edPK5Vk+attiRPdJbXJS0gvasdcYAqk=
Received: by 10.223.126.66 with SMTP id b2mr6789185fas.18.1231244729873;
	Tue, 06 Jan 2009 04:25:29 -0800 (PST)
Received: by 10.223.107.134 with HTTP; Tue, 6 Jan 2009 04:25:29 -0800 (PST)
Message-ID: <603c8f070901060425t485fb8d8nb79735ed72982d5a@mail.gmail.com>
Date: Tue, 6 Jan 2009 07:25:29 -0500
From: "Robert Haas" <robertmhaas@gmail.com>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Subject: Re: bad selectivity estimates for CASE
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <3032.1231216843@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <603c8f070901051915p15b3d6a7jbe6f6fdd178143e@mail.gmail.com>
	<3032.1231216843@sss.pgh.pa.us>
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/10
X-Sequence-Number: 32289

On Mon, Jan 5, 2009 at 11:40 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Robert Haas" <robertmhaas@gmail.com> writes:
>> 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.
>
> If you have an idea for a non-silly estimate, feel free to enlighten
> us...

Well, presumably CASE WHEN <expr1> THEN <constant1> WHEN <expr2> THEN
<constant2> WHEN <expr3> THEN <constant3> ... END = <constantn> could
be simplified to <exprn>.  But that's not going to happen in time to
do me any good on this query, if it ever happens (and might not be
sufficient anyway since the selectivity estimates of <expr1> may not
be very good either), so I was more looking for suggestions on coping
with the situation, since I'm sure that I'm not the first person to
have this type of problem.

...Robert