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@xxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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.
