The following bug has been logged online:
Bug reference: 1851
Logged by: Steve Wormley
Email address: steve@wormley.com
PostgreSQL version: 8.1beta1
Operating system: RedHat Linux, Kernel: 2.6.12.2
Description: Performance reduction from 8.0.3
Details:
Did a dump and restore of my DB to 8.1beta1 (running concurrently with
8.0.3) and performance on the same query is about 50% slower with the beta.
Ran query twice on each instance in sequence to make sure all data was in OS
cache.
8.0.3
explain analyze select 7208,avg(data.sl),mcc,mnc from grid_geography_link
as gglink , meta_data as meta, signal_level_stats as data where data.ogc_fid
= meta.ogc_fid AND data.isvalid=true AND meta.isvalid = true AND meta.gridid
= gglink.gridid and 7208 = gglink.geographyid group by mcc,mnc;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------------
HashAggregate (cost=36735.67..36736.24 rows=228 width=16) (actual
time=3834.630..3834.668 rows=8 loops=1)
-> Nested Loop (cost=16.19..36722.89 rows=1704 width=16) (actual
time=141.628..3526.391 rows=67824 loops=1)
-> Merge Join (cost=16.19..30737.86 rows=1766 width=16) (actual
time=141.565..1566.594 rows=68547 loops=1)
Merge Cond: ("outer".gridid = "inner".gridid)
-> Index Scan using meta_data_gridid on meta_data meta
(cost=0.00..35362.89 rows=312214 width=20) (actual time=0.103..499.071
rows=89081 loops=1)
Filter: (isvalid = true)
-> Sort (cost=16.19..16.76 rows=229 width=4) (actual
time=7.291..219.951 rows=68567 loops=1)
Sort Key: gglink.gridid
-> Index Scan using grid_geography_link_geographyid on
grid_geography_link gglink (cost=0.00..7.22 rows=229 width=4) (actual
time=0.077..3.865 rows=995 loops=1)
Index Cond: (7208 = geographyid)
-> Index Scan using signal_level_stats_pkey on signal_level_stats
data (cost=0.00..3.38 rows=1 width=16) (actual time=0.011..0.015 rows=1
loops=68547)
Index Cond: (data.ogc_fid = "outer".ogc_fid)
Filter: (isvalid = true)
Total runtime: 3834.868 ms
(14 rows)
8.1beta1:
explain analyze select 7208,avg(data.sl),mcc,mnc from grid_geography_link as
gglink , meta_data as meta, signal_level_stats as data where data.ogc_fid =
meta.ogc_fid AND data.isvalid=true AND meta.isvalid = true AND meta.gridid =
gglink.gridid and 7208 = gglink.geographyid group by mcc,mnc;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
------------------------
HashAggregate (cost=41180.34..41230.34 rows=20000 width=16) (actual
time=8834.251..8834.372 rows=8 loops=1)
-> Merge Join (cost=31059.37..37808.18 rows=449621 width=16) (actual
time=7969.032..8614.804 rows=67823 loops=1)
Merge Cond: ("outer".gridid = "inner".gridid)
-> Sort (cost=971.33..973.57 rows=896 width=4) (actual
time=7.344..10.284 rows=995 loops=1)
Sort Key: gglink.gridid
-> Bitmap Heap Scan on grid_geography_link gglink
(cost=6.13..927.39 rows=896 width=4) (actual time=0.289..3.687 rows=995
loops=1)
Recheck Cond: (7208 = geographyid)
-> Bitmap Index Scan on
grid_geography_link_geographyid (cost=0.00..6.13 rows=896 width=0) (actual
time=0.260..0.260 rows=995 loops=1)
Index Cond: (7208 = geographyid)
-> Sort (cost=30088.04..30338.95 rows=100362 width=20) (actual
time=7864.261..8124.173 rows=88143 loops=1)
Sort Key: meta.gridid
-> Hash Join (cost=8165.88..21750.54 rows=100362 width=20)
(actual time=2863.617..6363.921 rows=309036 loops=1)
Hash Cond: ("outer".ogc_fid = "inner".ogc_fid)
-> Bitmap Heap Scan on meta_data meta
(cost=1166.30..10165.86 rows=204656 width=20) (actual time=61.030..1271.910
rows=314108 loops=1)
Filter: isvalid
-> Bitmap Index Scan on meta_data_isvalid
(cost=0.00..1166.30 rows=204656 width=0) (actual time=58.417..58.417
rows=314108 loops=1)
Index Cond: (isvalid = true)
-> Hash (cost=6497.77..6497.77 rows=200724 width=16)
(actual time=2802.385..2802.385 rows=394772 loops=1)
-> Bitmap Heap Scan on signal_level_stats data
(cost=1144.53..6497.77 rows=200724 width=16) (actual time=71.196..1458.890
rows=394772 loops=1)
Filter: isvalid
-> Bitmap Index Scan on
signal_level_stats_isvalid (cost=0.00..1144.53 rows=200724 width=0) (actual
time=69.892..69.892 rows=394772 loops=1)
Index Cond: (isvalid = true)
Total runtime: 8884.601 ms
(23 rows)
(Of course, I was hoping with the new bitmaps things would be faster.)