Hi all,
I am running 9.03 with the settings listed below. I have a prohibitively slow query in an application which has an overall good performance:
select
cast (SD.detectorid as numeric),
CAST( ( (SD.createdtime - 0 )/ 18000000000000::bigint ) AS numeric) as timegroup,
sum(datafromsource)+sum(datafromdestination) as numbytes,
CAST ( sum(packetsfromsource)+sum(packetsfromdestination) AS numeric) as numpackets
from
appqosdata.icmptraffic SD , appqosdata.icmptraffic_classes CL
where
SD.detectorid >= 0
and CL.detectorid = SD.detectorid
and CL.sessionid = SD.sessionid
and CL.createdtime = SD.createdtime
and SD.detectorid = 1
and SD.createdtime >= 1317726000000000000::bigint and SD.createdtime < 1318326120000000000::bigint
and CL.sessionid < 1318326120000000000::bigint
and CL.classid = 1
group by
SD.detectorid, timegroup
appqosdata.icmptraffic and appqosdata.icmptraffic_classes are both partitioned.
CREATE TABLE appqosdata.icmptraffic
(
detectorid smallint not null default(0), -- references appqosdata.detectors(id),
sessionid bigint not null,
createdtime bigint not null,
...
--primary key(detectorid, sessionid, createdtime) defined in the children tables
);
CREATE TABLE appqosdata.icmptraffic_classes
(
detectorid smallint not null,
sessionid bigint not null,
createdtime bigint not null,
classid integer not null
-- definitions in the children tables:
--primary key(detectorid, sessionid, createdtime, classid)
--foreign key(detectorid, sessionid, createdtime) references appqosdata.icmptraffic(detectorid, sessionid, createdtime),
--foreign key(classid) references appqosdata.display_classes(id),
);
"HashAggregate (cost=154.24..154.28 rows=1 width=34) (actual time=7594069.940..7594069.983 rows=19 loops=1)"
" Output: (sd.detectorid)::numeric, ((((sd.createdtime - 0) / 18000000000000::bigint))::numeric), (sum(sd.datafromsource) + sum(sd.datafromdestination)), ((sum(sd.packetsfromsource) + sum(sd.packetsfromdestination)))::numeric, sd.detectorid"
" -> Nested Loop (cost=0.00..154.23 rows=1 width=34) (actual time=0.140..7593838.258 rows=50712 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, (((sd.createdtime - 0) / 18000000000000::bigint))::numeric"
" Join Filter: ((sd.sessionid = cl.sessionid) AND (sd.createdtime = cl.createdtime))"
" -> Append (cost=0.00..88.37 rows=7 width=18) (actual time=0.063..333.355 rows=51776 loops=1)"
" -> Seq Scan on appqosdata.icmptraffic_classes cl (cost=0.00..37.48 rows=1 width=18) (actual time=0.013..0.013 rows=0 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Filter: ((cl.sessionid < 1318326120000000000::bigint) AND (cl.detectorid = 1) AND (cl.classid = "">" -> Index Scan using icmptraffic_classes_10_pkey on appqosdata.icmptraffic_classes_10 cl (cost=0.00..8.36 rows=1 width=18) (actual time=0.046..14.205 rows=3985 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Index Cond: ((cl.detectorid = 1) AND (cl.sessionid < 1318326120000000000::bigint) AND (cl.classid = "">
" -> Index Scan using icmptraffic_classes_11_pkey on appqosdata.icmptraffic_classes_11 cl (cost=0.00..8.62 rows=1 width=18) (actual time=0.038..52.757 rows=14372 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Index Cond: ((cl.detectorid = 1) AND (cl.sessionid < 1318326120000000000::bigint) AND (cl.classid = "">" -> Index Scan using icmptraffic_classes_12_pkey on appqosdata.icmptraffic_classes_12 cl (cost=0.00..8.60 rows=1 width=18) (actual time=0.033..47.845 rows=13512 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Index Cond: ((cl.detectorid = 1) AND (cl.sessionid < 1318326120000000000::bigint) AND (cl.classid = "">
" -> Index Scan using icmptraffic_classes_13_pkey on appqosdata.icmptraffic_classes_13 cl (cost=0.00..8.59 rows=1 width=18) (actual time=0.030..46.504 rows=13274 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Index Cond: ((cl.detectorid = 1) AND (cl.sessionid < 1318326120000000000::bigint) AND (cl.classid = "">" -> Index Scan using icmptraffic_classes_14_pkey on appqosdata.icmptraffic_classes_14 cl (cost=0.00..8.43 rows=1 width=18) (actual time=0.025..22.868 rows=6633 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Index Cond: ((cl.detectorid = 1) AND (cl.sessionid < 1318326120000000000::bigint) AND (cl.classid = "">
" -> Index Scan using icmptraffic_classes_15_pkey on appqosdata.icmptraffic_classes_15 cl (cost=0.00..8.30 rows=1 width=18) (actual time=0.014..0.014 rows=0 loops=1)"
" Output: cl.detectorid, cl.sessionid, cl.createdtime"
" Index Cond: ((cl.detectorid = 1) AND (cl.sessionid < 1318326120000000000::bigint) AND (cl.classid = "">" -> Materialize (cost=0.00..65.13 rows=6 width=42) (actual time=0.001..73.261 rows=50915 loops=51776)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" -> Append (cost=0.00..65.10 rows=6 width=42) (actual time=0.059..244.693 rows=50915 loops=1)"
" -> Seq Scan on appqosdata.icmptraffic sd (cost=0.00..22.60 rows=1 width=42) (actual time=0.001..0.001 rows=0 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" Filter: ((sd.detectorid >= 0) AND (sd.createdtime >= 1317726000000000000::bigint) AND (sd.createdtime < 1318326120000000000::bigint) AND (sd.detectorid = 1))"
" -> Index Scan using icmptraffic_10_pkey on appqosdata.icmptraffic_10 sd (cost=0.00..8.35 rows=1 width=42) (actual time=0.053..7.807 rows=3997 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" Index Cond: ((sd.detectorid >= 0) AND (sd.detectorid = 1) AND (sd.createdtime >= 1317726000000000000::bigint) AND (sd.createdtime < 1318326120000000000::bigint))"
" -> Index Scan using icmptraffic_11_pkey on appqosdata.icmptraffic_11 sd (cost=0.00..8.59 rows=1 width=42) (actual time=0.025..27.957 rows=14372 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" Index Cond: ((sd.detectorid >= 0) AND (sd.detectorid = 1) AND (sd.createdtime >= 1317726000000000000::bigint) AND (sd.createdtime < 1318326120000000000::bigint))"
" -> Index Scan using icmptraffic_12_pkey on appqosdata.icmptraffic_12 sd (cost=0.00..8.58 rows=1 width=42) (actual time=0.027..26.217 rows=13512 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" Index Cond: ((sd.detectorid >= 0) AND (sd.detectorid = 1) AND (sd.createdtime >= 1317726000000000000::bigint) AND (sd.createdtime < 1318326120000000000::bigint))"
" -> Index Scan using icmptraffic_13_pkey on appqosdata.icmptraffic_13 sd (cost=0.00..8.56 rows=1 width=42) (actual time=0.030..26.075 rows=13430 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" Index Cond: ((sd.detectorid >= 0) AND (sd.detectorid = 1) AND (sd.createdtime >= 1317726000000000000::bigint) AND (sd.createdtime < 1318326120000000000::bigint))"
" -> Index Scan using icmptraffic_14_pkey on appqosdata.icmptraffic_14 sd (cost=0.00..8.41 rows=1 width=42) (actual time=0.027..11.040 rows=5604 loops=1)"
" Output: sd.detectorid, sd.createdtime, sd.datafromsource, sd.datafromdestination, sd.packetsfromsource, sd.packetsfromdestination, sd.sessionid"
" Index Cond: ((sd.detectorid >= 0) AND (sd.detectorid = 1) AND (sd.createdtime >= 1317726000000000000::bigint) AND (sd.createdtime < 1318326120000000000::bigint))"
"Total runtime: 7594071.137 ms"
name | current_setting
------------------------------+----------------------------------------------------------------------------------------------------------
version | PostgreSQL 9.0.3 on amd64-portbld-freebsd8.0, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit
autovacuum | on
autovacuum_analyze_threshold | 500000
autovacuum_max_workers | 1
autovacuum_naptime | 1h
autovacuum_vacuum_threshold | 500000
checkpoint_segments | 64
effective_cache_size | 3GB
fsync | on
lc_collate | C
lc_ctype | C
listen_addresses | *
log_destination | syslog, stderr
log_min_duration_statement | 5ms
log_rotation_age | 1d
log_rotation_size | 100MB
logging_collector | on
max_connections | 30
max_stack_depth | 2MB
server_encoding | UTF8
shared_buffers | 1793MB
silent_mode | on
synchronous_commit | on
syslog_facility | local0
TimeZone | Europe/Jersey
update_process_title | off
wal_buffers | 128kB
work_mem | 24MB
Thanks for any help,
Svetlin Manavski
