Re: reducing number of ANDs speeds up query

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(dot)e(dot)lawrence(at)icloud(dot)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(at)postgresql(dot)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.