Does any one can tell me why the same query runs against on smaller data is slower than bigger table. thanks very much.
I am using PostgreSQL9.1.8.
t_apps_1 and t_estimate_1 are about 300M respectively, while *_list_1 about 10M more or less. According to the result, it need to read a lot of blocks(112) from disk.
explain (ANALYZE ON, BUFFERS ON, verbose on
) SELECT e.t_id, SUM(e.estimate) as est
FROM
t_estimate_list_1 l,
t_apps_list_1 rl,
t_apps_1 r,
t_estimate_1 e
WHERE
l.id = rl.dsf_id and
l.date = '2012-07-01' and
l.fed_id = 202 and
l.st_id = 143464 and
rl.cat_id = 12201 and
l.id = e.list_id and
rl.id = r.list_id and
r.t_id = e.t_id
GROUP BY e.t_id;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2529.91..2530.06 rows=15 width=8) (actual time=1041.391..1041.409 rows=97 loops=1)
Buffers: shared hit=304 read=112
-> Nested Loop (cost=0.00..2529.84 rows=15 width=8) (actual time=96.752..1041.145 rows=97 loops=1)
Buffers: shared hit=304 read=112
-> Nested Loop (cost=0.00..312.60 rows=242 width=12) (actual time=62.035..70.239 rows=97 loops=1)
Buffers: shared hit=18 read=10
-> Nested Loop (cost=0.00..16.56 rows=1 width=12) (actual time=19.520..19.521 rows=1 loops=1)
Buffers: shared hit=3 read=6
-> Index Scan using t_estimate_list_1_unique on t_estimate_list_1 l (cost=0.00..8.27 rows=1 width=4) (actual time=11.175..11.176 rows=1 loops=1)
Index Cond: ((date = '2012-07-01'::date) AND (st_id = 143464) AND (fed_id = 202))
Buffers: shared hit=2 read=4
-> Index Scan using t_apps_list_1_unique on t_apps_list_1 rl (cost=0.00..8.28 rows=1 width=8) (actual time=8.339..8.339 rows=1 loops=1)
Index Cond: ((dsf_id = l.id) AND (cat_id = 12201))
Buffers: shared hit=1 read=2
-> Index Scan using t_apps_1_pkey on t_apps_1 r (cost=0.00..288.56 rows=598 width=8) (actual time=42.513..50.676 rows=97 loops=1)
Index Cond: (list_id = rl.id)
Buffers: shared hit=15 read=4
-> Index Scan using t_estimate_1_pkey on t_estimate_1 e (cost=0.00..9.15 rows=1 width=12) (actual time=10.006..10.007 rows=1 loops=97)
Index Cond: ((list_id = l.id) AND (t_id = r.t_id))
Buffers: shared hit=286 read=102
Total runtime: 1041.511 ms
(21 rows)
The table *_30 are about 30 times larger than *_1 in the above SQL. According to the result, it need to read a lot of blocks(22) from disk.
explain (ANALYZE ON, BUFFERS ON
) SELECT e.t_id, SUM(e.estimate) as est
FROM
t_estimate_list_30 l,
t_apps_list_30 rl,
t_apps_30 r,
t_estimate_30 e
WHERE
l.id = rl.dsf_id and
l.date = '2012-07-01' and
l.fed_id = 202 and
l.st_id = 143464 and
rl.cat_id = 12201 and
l.id = e.list_id and
rl.id = r.list_id and
r.t_id = e.t_id
GROUP BY e.t_id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=3494.89..3495.04 rows=15 width=8) (actual time=160.612..160.632 rows=97 loops=1)
Buffers: shared hit=493 read=22
-> Nested Loop (cost=0.00..3494.81 rows=15 width=8) (actual time=151.183..160.533 rows=97 loops=1)
Buffers: shared hit=493 read=22
-> Nested Loop (cost=0.00..431.42 rows=240 width=12) (actual time=105.810..106.597 rows=97 loops=1)
Buffers: shared hit=20 read=10
-> Nested Loop (cost=0.00..16.58 rows=1 width=12) (actual time=52.804..52.805 rows=1 loops=1)
Buffers: shared hit=4 read=6
-> Index Scan using t_estimate_list_5_unique on t_estimate_list_5 l (cost=0.00..8.27 rows=1 width=4) (actual time=19.846..19.846 rows=1 loops=1)
Index Cond: ((date = '2012-07-01'::date) AND (st_id = 143464) AND (fed_id = 202))
Buffers: shared hit=2 read=4
-> Index Scan using t_apps_list_5_unique on t_apps_list_5 rl (cost=0.00..8.30 rows=1 width=8) (actual time=32.951..32.952 rows=1 loops=1)
Index Cond: ((dsf_id = l.id) AND (cat_id = 12201))
Buffers: shared hit=2 read=2
-> Index Scan using t_apps_5_pkey on t_apps_5 r (cost=0.00..393.68 rows=1693 width=8) (actual time=53.004..53.755 rows=97 loops=1)
Index Cond: (list_id = rl.id)
Buffers: shared hit=16 read=4
-> Index Scan using t_estimate_5_pkey on t_estimate_5 e (cost=0.00..12.75 rows=1 width=12) (actual time=0.555..0.555 rows=1 loops=97)
Index Cond: ((list_id = l.id) AND (t_id = r.t_id))
Buffers: shared hit=473 read=12
Total runtime: 160.729 ms
(21 rows)
