Hi!

I found two queries which do the same thing but they is very different in time. For test suite it's about 10^3 times, but on real data it can be 10^5 times. It's observed on 8.1-current, 8.2-current and CVS HEAD versions. Interesting that even without LIMIT clause they take approximately the same time, but costs is differ in 30 times. Is any way to tweaking pgsql to produce more reasonable plan for first query?


This query is auto-generated, so they may be more complex and I choose simplest example.

First query:
explain analyze
select *
from
    a
    left outer join (
        select b.id, sum(b.val)
        from b
        group by b.id
    ) bagg
        on bagg.id = a.id
where
    a.id > 10000
order by a.addon, a.id
limit 100;
Limit (cost=9923.36..9923.61 rows=100 width=20) (actual time=2232.437..2233.273 rows=100 loops=1) -> Sort (cost=9923.36..10031.41 rows=43221 width=20) (actual time=2232.428..2232.709 rows=100 loops=1)
         Sort Key: a.addon, a.id
         Sort Method:  top-N heapsort  Memory: 24kB
-> Merge Right Join (cost=0.00..8271.48 rows=43221 width=20) (actual time=313.198..2052.559 rows=40000 loops=1)
               Merge Cond: (b.id = a.id)
-> GroupAggregate (cost=0.00..5725.41 rows=53292 width=12) (actual time=0.266..1422.522 rows=50000 loops=1) -> Index Scan using bidx on b (cost=0.00..4309.26 rows=150000 width=12) (actual time=0.217..547.402 rows=150000 loops=1) -> Index Scan using a1idx on a (cost=0.00..1256.90 rows=40551 width=8) (actual time=0.171..155.073 rows=40000 loops=1)
                     Index Cond: (a.id > 10000)
 Total runtime: 2233.940 ms

Second query:
explain analyze
select
    a.id,
    (
        select sum(b.val)
        from b
        where b.id = a.id
    ) as val
from a
where
    id > 10000
order by a.addon, a.id
limit 100;

Limit (cost=0.00..839.04 rows=100 width=8) (actual time=0.339..7.436 rows=100 loops=1) -> Index Scan using a2idx on a (cost=0.00..340241.08 rows=40551 width=8) (actual time=0.332..6.865 rows=100 loops=1)
         Index Cond: (id > 10000)
         SubPlan
-> Aggregate (cost=8.33..8.34 rows=1 width=8) (actual time=0.048..0.051 rows=1 loops=100) -> Index Scan using bidx on b (cost=0.00..8.32 rows=3 width=8) (actual time=0.016..0.027 rows=3 loops=100)
                       Index Cond: (id = $0)


How to reproduce:
select generate_series as id, (random()*100)::int as addon into a
    from generate_series(1,50000);
create unique index a1idx on a (id);
create unique index a2idx on a (addon, id);

select
    id, random() as val into b
    from generate_series(1,50000) as id , generate_series(1,3) as foo;
create index bidx on b (id);

vacuum analyze;


--
Teodor Sigaev                                   E-mail: [EMAIL PROTECTED]
                                                   WWW: http://www.sigaev.ru/

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to