Postgres Pro
Downloads
Home   >   mailing lists

Re: reducing number of ANDs speeds up query - Mailing list pgsql-general

From Alban Hertroys
Subject Re: reducing number of ANDs speeds up query
Date
Msg-id CAF-3MvM+s-Spc5fXs7NwvR=gNW=-Q5acsKK=CTBgeHFXbDzrPw@mail.gmail.com
Whole thread Raw
In response to reducing number of ANDs speeds up query  ("T. E. Lawrence" <t.e.lawrence@icloud.com>)
Responses Re: reducing number of ANDs speeds up query  ("T. E. Lawrence" <t.e.lawrence@icloud.com>)
List pgsql-general
Tree view
You really ought to include the output of EXPLAIN ANALYZE in cases such as these (if it doesn't already point you to the culprit).

Most likely you'll find that the last condition added a sequential scan to the query plan, which can have several causes/reasons. Are the estimated #rows close to the actual #rows? Is b.value indexed? How selective is the value you're matching it against (is it uncommon or quite common)? Etc, etc.

Meanwhile, it looks like most of your AND's are involved in joining tables a and b. Perhaps it helps to use an explicit join instead of an implicit one?


On 12 January 2013 02:47, 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), because the 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_b contains 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?

Thank you and kind regards,
T.


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



--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

pgsql-general by date:

Previous
From: Amit kapila
Date:
Subject: Re: reducing number of ANDs speeds up query
Next
From: Eduardo Morras
Date:
Subject: Re: reducing number of ANDs speeds up query