Message-ID: <4C906706.40108@thl.fi>
Date: Wed, 15 Sep 2010 09:26:14 +0300
From: =?ISO-8859-1?Q?Anssi_K=E4=E4ri=E4inen?= <anssi.kaariainen@thl.fi>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.1.12) Gecko/20100826 Thunderbird/3.0.7
MIME-Version: 1.0
To: <pgsql-performance@postgresql.org>
Subject: Performance problem with joined aggregate query
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: quoted-printable

Hello all,

I am trying to use aggregate queries in views, and when joining these=20
views to other
tables, I get seq scan in the view, even if index scan would be clearly=20
better. The views
I am using in my Db are actually long pivot queries, but the following=20
simple test case is enough
to show the problem.

I will first show the table definitions, then the performance problem I=20
am having.

create table test1 (
    id serial primary key not null,
    other_id integer unique not null
);

create table test2 (
     id integer not null references test1(id),
     type integer,
     value text
);

create index test2_idx on test2(id);

insert into test1 select g, g+10000 from (select generate_series(1,=20
10000) as g) t;
insert into test2 select g, g%3, 'testval'||g from (select=20
generate_series(1, 10000) as g) t;
insert into test2 select g, (g+1)%3, 'testval'||g from (select=20
generate_series(1, 10000) as g) t;
insert into test2 select g, (g+2)%3, 'testval'||g from (select=20
generate_series(1, 10000) as g) t;

Now, the following query is fast:

select * from test1 inner join (select array_agg(value), id
from test2 group by id) t on test1.id =3D t.id where test1.id =3D 1;
(0.6ms)

But the following query is slow (seqscan on test2):

select * from test1 inner join (select array_agg(value), id
from test2 group by id) t on test1.id =3D t.id where test1.other_id =3D 1=
0001;
(45ms)

The same problem can be seen when running:

select * from test1 inner join (select array_agg(value), id
from test2 group by id) t on test1.id =3D t.id where test1.id in (1, 2);
(40ms runtime)

Fetching directly from test2 with id is fast:

select array_agg(value), id
from test2 where test2.id in (1, 2) group by id;

If I set enable_seqscan to off, then I get fast results:

select * from test1 inner join (select array_agg(value), id
from test2 group by id) t on test1.id =3D t.id where test1.other_id in=20
(10001, 10002);
(0.6ms)

Or slow results, if the fetched rows happen to be in the end of the index=
:

select * from test1 inner join (select array_agg(value), id
from test2 group by id) t on test1.id =3D t.id where test1.other_id =3D 2=
0000;
(40ms)

Explain analyzes of the problematic query:

With enable_seqscan:

explain analyze select * from test1 inner join (select array_agg(value), =
id
from test2 group by id) t on test1.id =3D t.id where test1.other_id =3D 1=
0001;

Hash Join  (cost=3D627.48..890.48 rows=3D50 width=3D44) (actual=20
time=3D91.575..108.085 rows=3D1 loops=3D1)
    Hash Cond: (test2.id =3D test1.id)
    ->  HashAggregate  (cost=3D627.00..752.00 rows=3D10000 width=3D15) (a=
ctual=20
time=3D82.663..98.281 rows=3D10000 loops=3D1)
          ->  Seq Scan on test2  (cost=3D0.00..477.00 rows=3D30000 width=3D=
15)=20
(actual time=3D0.009..30.650 rows=3D30000 loops=3D1)
    ->  Hash  (cost=3D0.47..0.47 rows=3D1 width=3D8) (actual time=3D0.026=
..0.026=20
rows=3D1 loops=3D1)
          ->  Index Scan using test1_other_id_key on test1 =20
(cost=3D0.00..0.47 rows=3D1 width=3D8) (actual time=3D0.018..0.021 rows=3D=
1 loops=3D1)
                Index Cond: (other_id =3D 10001)
Total runtime: 109.686 ms

Without enable_seqscan:

explain analyze select * from test1 inner join (select array_agg(value), =
id
from test2 group by id) t on test1.id =3D t.id where test1.other_id =3D 1=
0001;

Merge Join  (cost=3D0.48..895.91 rows=3D50 width=3D44) (actual=20
time=3D0.066..0.085 rows=3D1 loops=3D1)
    Merge Cond: (test2.id =3D test1.id)
    ->  GroupAggregate  (cost=3D0.00..769.56 rows=3D10000 width=3D15) (ac=
tual=20
time=3D0.040..0.054 rows=3D2 loops=3D1)
          ->  Index Scan using test2_idx on test2  (cost=3D0.00..494.56=20
rows=3D30000 width=3D15) (actual time=3D0.017..0.030 rows=3D7 loops=3D1)
    ->  Sort  (cost=3D0.48..0.48 rows=3D1 width=3D8) (actual time=3D0.020=
..0.022=20
rows=3D1 loops=3D1)
          Sort Key: test1.id
          Sort Method:  quicksort  Memory: 17kB
          ->  Index Scan using test1_other_id_key on test1 =20
(cost=3D0.00..0.47 rows=3D1 width=3D8) (actual time=3D0.010..0.012 rows=3D=
1 loops=3D1)
                Index Cond: (other_id =3D 10001)


  - Anssi K=E4=E4ri=E4inen