Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49733=pgsql-performance-ar@postgresql.org>)
	id 1USq75-0003WZ-8X for pgsql-performance@arkaria.postgresql.org;
	Thu, 18 Apr 2013 14:47:03 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.72) (envelope-from
	<pgsql-performance-owner+M49733=pgsql-performance-ar@postgresql.org>)
	id 1USq74-0001bp-FI for pgsql-performance@arkaria.postgresql.org;
	Thu, 18 Apr 2013 14:47:02 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
	by malur.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1USq73-0001bY-M0
	for pgsql-performance@postgresql.org; Thu, 18 Apr 2013 14:47:01 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
	by makus.postgresql.org with esmtp (Exim 4.72)
	(envelope-from <tgl@sss.pgh.pa.us>) id 1USq71-00087y-86
	for pgsql-performance@postgresql.org; Thu, 18 Apr 2013 14:47:01 +0000
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r3IEkvLX002375;
	Thu, 18 Apr 2013 10:46:57 -0400 (EDT)
From: Tom Lane <tgl@sss.pgh.pa.us>
To: dmitry potapov <potapov.dmitry@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: "WHERE 1 = 2 OR ..." makes planner choose a very inefficient plan
In-reply-to: 
 <CAJPZb_vmf3dkCv0Rre+VOm6bkJc3hugjEE2h0bgY1WqduDLUbg@mail.gmail.com>
References: 
 <CAJPZb_vmf3dkCv0Rre+VOm6bkJc3hugjEE2h0bgY1WqduDLUbg@mail.gmail.com>
Comments: In-reply-to dmitry potapov <potapov.dmitry@gmail.com>
	message dated "Thu, 18 Apr 2013 18:20:48 +0400"
Date: Thu, 18 Apr 2013 10:46:57 -0400
Message-ID: <2374.1366296417@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

dmitry potapov <potapov.dmitry@gmail.com> writes:
> I recently stumbled upon on what could be a planner bug or a corner case.
> If "<false condition> OR ..." is added to WHERE clause of SELECT query,
> then the planner chooses a very inefficient plan. Consider a query:

> SELECT count(k0.id)
> FROM k0
> WHERE 1 = 2
>     OR k0.id IN (
>         SELECT k1.k0_id
>         FROM k1
>         WHERE k1.k1k2_id IN (
>                 SELECT k2.k1k2_id
>                 FROM k2
>                 WHERE k2.t = 2
>                     AND (coalesce(k2.z, '')) LIKE '%12%'
>                 )
>         );

Perhaps you should fix your application to not generate such incredibly
silly SQL.  Figuring out that 1=2 is constant false and throwing it away
costs the server easily a thousand times as many instructions as it
would take for the client to not emit that in the first place.

The reason you don't get a nice semijoin plan when you do that is that
conversion of IN clauses to semijoins happens before
constant-subexpression simplification.  So the planner hasn't yet
figured out that the OR is useless when it would need to know that to
produce a good plan.  (And no, we can't just flip the order of those two
steps.  Doing two rounds of const-simplification wouldn't be a good
answer either, because it would penalize well-written queries to benefit
badly-written ones.)

			regards, tom lane


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