Date: Fri, 09 Feb 2007 00:01:59 -0500
From: Joe <dev@freedomcircle.net>
Subject: Re: huge disparities in =/IN/BETWEEN performance
In-reply-to: <14627.1170995084@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alvaro Herrera <alvherre@commandprompt.com>,
	George Pavlov <gpavlov@mynewplace.com>, pgsql-sql@postgresql.org
Message-id: <1170997319.697.72.camel@pampa>
Organization: Freedom Circle, LLC
MIME-version: 1.0
Content-type: text/plain
Content-transfer-encoding: 7bit
References: 
 <8C5B026B51B6854CBE88121DBF097A86815F42@ehost010-33.exch010.intermedia.net>
	<7377.1170986068@sss.pgh.pa.us>
	<8C5B026B51B6854CBE88121DBF097A86815F81@ehost010-33.exch010.intermedia.net>
	<20070209021414.GU24069@alvh.no-ip.org>
	<14273.1170993005@sss.pgh.pa.us>
	<1170994335.697.55.camel@pampa>  <14627.1170995084@sss.pgh.pa.us>

Hi Tom,

On Thu, 2007-02-08 at 23:24 -0500, Tom Lane wrote:
> Certainly, but your other concerns don't follow from that.  The issue at
> hand here is whether it's worth expending cycles on every query to try
> to detect a situation that only holds for a few.

Those where George's concerns, but I was interested in knowing whether
the planner transforms a query in any way to avoid, let's say, useless
execution.  George didn't provide the inside of his view, but it's
possible that my earlier example could be rephrased as follows:

create view v_foo as select * from tab where x < 5;
select * from v_foo where x = 10;

Presumably the planner has to transform the SELECT into

select * from tab where x < 5 and x = 10;

or something analogous.  This is a simple example, but with complicated
views or even joins and aggregates, the "useless execution" may not be
that obvious to the "naked eye" but it would be to a boolean logic
analyzer.

As to whether these query instances represent few or are typical is
arguable, and will depend on the type of application, level of knowledge
among users, and what kind of interfaces are used to enter or generate
the queries.

Joe