Re: A query become very slow after upgrade from 8.1.10 to 8.4.5 - Mailing list pgsql-performance

From Tom Lane
Subject Re: A query become very slow after upgrade from 8.1.10 to 8.4.5
Date
Msg-id 17301.1288737008@sss.pgh.pa.us
Whole thread Raw
In response to A query become very slow after upgrade from 8.1.10 to 8.4.5  (Yaocl <chunlinyao@gmail.com>)
Responses Re: A query become very slow after upgrade from 8.1.10 to 8.4.5  (Yaocl <chunlinyao@gmail.com>)
List pgsql-performance
Yaocl <chunlinyao@gmail.com> writes:
> SELECT t_a.id FROM t_a
> WHERE EXISTS ( SELECT t_b.id FROM t_b, t_c
>                WHERE t_b.id = t_a.id AND t_c.flag = 'f')

I have some hopes for fixing this in 9.1, but nothing is going to happen
in 8.4 or 9.0.  In the meantime, is it intentional that there is no join
clause between t_b and t_c?  That'd be a lot more efficient as two
separate EXISTS tests, ie

WHERE EXISTS ( SELECT 1 FROM t_b WHERE t_b.id = t_a.id ) AND
      EXISTS ( SELECT 1 FROM t_c WHERE t_c.flag = 'f')

but I wonder whether this query doesn't simply reflect a logic error on
the client side.

            regards, tom lane

pgsql-performance by date:

Previous
From: Mladen Gogala
Date:
Subject: Array interface
Next
From: Conor Walsh
Date:
Subject: Re: Array interface