Re: reducing number of ANDs speeds up query

On Saturday, January 12, 2013 7:17 AM T. E. Lawrence 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? 

You can try once with below query:
Select * from (SELECT a.id,b.value FROM table_a a, table_b b WHERE ... AND ... ) X where X.value=...;

If this doesn't work can you send the Explain .. output for both queries(the query you are using and the query I have
suggested)


With Regards,
Amit Kapila.