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

From: Yaocl <chunlinyao(at)gmail(dot)com>
To:
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: A query become very slow after upgrade from 8.1.10 to 8.4.5
Date: 2010-11-03 01:47:06
Message-ID: AANLkTikHKgChQxgq=R-uY-ECFsyO8f3aL_GXki0BX3Fp@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Wed, Nov 3, 2010 at 6:30 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Yaocl <chunlinyao(at)gmail(dot)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

In response to

Browse pgsql-performance by date

  From Date Subject
Next Message Mladen Gogala 2010-11-03 14:56:51 Re: Array interface
Previous Message Conor Walsh 2010-11-02 22:40:32 Re: Array interface