On 12/01/2013, at 12:47 PM, T. E. Lawrence <t.e.lawre...@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? 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 I think 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
