Re: A query become very slow after upgrade from 8.1.10 to 8.4.5

On Wed, Nov 3, 2010 at 6:30 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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
>
Yes ,If I moved t_c to another clause, It can resolve this problem.
The original sql is generate by a orm.Has some connection between t_b
and t_c.Like this:
AND exists ( SELECT t_b.id from t_b, t_c
                 WHERE t_b.id = t_a.id
                 AND t_c.some_field <= t_b.some_field )
How ever this is still a poor query.

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'
               AND t_b.id < t_c.id)

8.1.10
Seq Scan on t_a  (cost=0.00..50.87 rows=300 width=4) (actual
time=0.021..5.367 rows=600 loops=1)
 Filter: (subplan)
 SubPlan
   ->  Nested Loop  (cost=0.00..137.19 rows=2014 width=4) (actual
time=0.007..0.007 rows=1 loops=601)
         ->  Index Scan using t_b_pkey on t_b  (cost=0.00..3.02
rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=601)
               Index Cond: (id = $0)
         ->  Index Scan using t_c_pkey on t_c  (cost=0.00..109.00
rows=2014 width=4) (actual time=0.003..0.003 rows=1 loops=600)
               Index Cond: (outer.id <= t_c.id)
               Filter: (NOT flag)
Total runtime: 5.564 ms

8.4.5
Nested Loop Semi Join  (cost=0.00..154223.42 rows=601 width=4) (actual
time=0.037..38727.982 rows=600 loops=1)
 Join Filter: (t_a.id = t_b.id)
 ->  Seq Scan on t_a  (cost=0.00..9.01 rows=601 width=4) (actual
time=0.011..0.237 rows=601 loops=1)
 ->  Nested Loop  (cost=0.00..182995.83 rows=6042000 width=4) (actual
time=0.009..49.298 rows=57594 loops=601)
       ->  Seq Scan on t_c  (cost=0.00..174.00 rows=6042 width=4)
(actual time=0.005..0.085 rows=169 loops=601)
             Filter: (NOT flag)
       ->  Index Scan using t_b_pkey on t_b  (cost=0.00..17.76
rows=1000 width=4) (actual time=0.007..0.132 rows=342 loops=101296)
             Index Cond: (t_b.id <= t_c.id)
Total runtime: 38728.263 ms

finally I rewritten the orm query to generate a different sql.

Regards,
Yao