Re: reducing number of ANDs speeds up query

On 12/01/2013, at 12:47 PM, T. E. Lawrence <t.e.lawrence@icloud.com> wrote:

> Hello,
>
> I have a pretty standard query with two tables:
>
> SELECT table_a.id FROM table_a a, table_b b WHERE ... AND ... AND b.value=...;
>
> With the last "AND b.value=..." the query is extremely slow (did not wait for it to end, but more than a minute),
becausethe value column is not indexed (contains items longer than 8K). 
>
> However the previous conditions "WHERE ... AND ... AND" should have already reduced the candidate rows to just a few
(table_bcontains over 50m rows). And indeed, removing the last "AND b.value=..." speeds the query to just a
millisecond.
>
> Is there a way to instruct PostgreSQL to do first the initial "WHERE ... AND ... AND" and then the last "AND
b.value=..."on the (very small) result? 

Have you looked at the WITH clause [1,2]:

WITH filtered as (SELECT table_a.id, b.value as val FROM table_a a, table_b b WHERE … AND …)
SELECT * FROM filtered WHERE filtered.val=…

It evaluates the the first SELECT once, then applies the second SELECT to the first in memory (at least that's the way
Ithink about them). 

Cheers,

Tony


[1] http://www.postgresql.org/docs/9.2/static/queries-with.html
[2] http://www.postgresql.org/docs/9.2/static/sql-select.html#SQL-WITH