Re: huge disparities in =/IN/BETWEEN performance

From: Joe <dev(at)freedomcircle(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, George Pavlov <gpavlov(at)mynewplace(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: huge disparities in =/IN/BETWEEN performance
Date: 2007-02-09 05:01:59
Message-ID: 1170997319.697.72.camel@pampa
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Patrice Oliver 2007-02-09 08:26:52 unsubscribe
Previous Message Arulmani V A 2007-02-09 04:25:58 unsubscribe