On Wed, Nov 3, 2010 at 6:30 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Yaocl <chunlinyao@xxxxxxxxx> 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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
