I'm wondering if there's any way I can tweak things so that the estimate
for the query is more accurate (I have run analyze):
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=2712755.92..2713043.69 rows=12790 width=24)
-> Nested Loop (cost=2997.45..2462374.58 rows=9104776 width=24)
Join Filter: (("outer".prev_end_time < ms_t("inner".tick)) AND ("outer".end_time >= ms_t("inner".tick)))
-> Seq Scan on bucket b (cost=0.00..51.98 rows=1279 width=20)
Filter: ((rrd_id = 1) AND (end_time <= '2004-11-09 16:04:00-06'::timestamp with time zone) AND (end_time > '2004-11-08 16:31:00-06'::timestamp with time zone))
-> Materialize (cost=2997.45..3638.40 rows=64095 width=28)
-> Hash Join (cost=94.31..2997.45 rows=64095 width=28)
Hash Cond: ("outer".alert_def_id = "inner".id)
-> Seq Scan on alert (cost=0.00..1781.68 rows=64068 width=28)
-> Hash (cost=88.21..88.21 rows=2440 width=8)
-> Hash Join (cost=1.12..88.21 rows=2440 width=8)
Hash Cond: ("outer".alert_type_id = "inner".id)
-> Seq Scan on alert_def d (cost=0.00..44.39 rows=2439 width=8)
-> Hash (cost=1.10..1.10 rows=10 width=4)
-> Seq Scan on alert_type t (cost=0.00..1.10 rows=10 width=4)
(15 rows)
opensims=# set enable_seqscan=false;
SET
opensims=# explain analyze SELECT a.rrd_bucket_id, alert_type_id
opensims-# , count(*), count(*), count(*), min(ci), max(ci), sum(ci), min(rm), max(rm), sum(rm)
opensims-# FROM
opensims-# (SELECT b.bucket_id AS rrd_bucket_id, s.*
opensims(# FROM rrd.bucket b
opensims(# JOIN alert_def_type_v s
opensims(# ON (
opensims(# b.prev_end_time < tick_tsz
opensims(# AND b.end_time >= tick_tsz )
opensims(# WHERE b.rrd_id = '1'
opensims(# AND b.end_time <= '2004-11-09 16:04:00-06'
opensims(# AND b.end_time > '2004-11-08 16:31:00-06'
opensims(# ) a
opensims-# GROUP BY rrd_bucket_id, alert_type_id;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=3787628.37..3787916.15 rows=12790 width=24) (actual time=202.045..215.197 rows=5234 loops=1)
-> Hash Join (cost=107.76..3537247.03 rows=9104776 width=24) (actual time=10.728..147.415 rows=17423 loops=1)
Hash Cond: ("outer".alert_def_id = "inner".id)
-> Nested Loop (cost=0.00..3377768.38 rows=9104775 width=24) (actual time=0.042..93.512 rows=17423 loops=1)
-> Index Scan using rrd_bucket__rrd_id__end_time on bucket b (cost=0.00..101.62 rows=1279 width=20) (actual time=0.018..3.040 rows=1413 loops=1)
Index Cond: ((rrd_id = 1) AND (end_time <= '2004-11-09 16:04:00-06'::timestamp with time zone) AND (end_time > '2004-11-08 16:31:00-06'::timestamp with time zone))
-> Index Scan using alert__tick_tsz on alert (cost=0.00..2498.49 rows=7119 width=28) (actual time=0.006..0.030 rows=12 loops=1413)
Index Cond: (("outer".prev_end_time < ms_t(alert.tick)) AND ("outer".end_time >= ms_t(alert.tick)))
-> Hash (cost=101.66..101.66 rows=2440 width=8) (actual time=10.509..10.509 rows=0 loops=1)
-> Hash Join (cost=3.13..101.66 rows=2440 width=8) (actual time=0.266..8.499 rows=2439 loops=1)
Hash Cond: ("outer".alert_type_id = "inner".id)
-> Index Scan using alert_def_pkey on alert_def d (cost=0.00..55.83 rows=2439 width=8) (actual time=0.009..3.368 rows=2439 loops=1)
-> Hash (cost=3.11..3.11 rows=10 width=4) (actual time=0.061..0.061 rows=0 loops=1)
-> Index Scan using alert_type_pkey on alert_type t (cost=0.00..3.11 rows=10 width=4) (actual time=0.018..0.038 rows=10 loops=1)
Total runtime: 218.644 ms
(15 rows)
opensims=#
I'd really like to avoid putting a 'set enable_seqscan=false' in my
code, especially since this query only has a problem if it's run on a
large date/time window, which normally doesn't happen.
--
Jim C. Nasby, Database Consultant decibel@xxxxxxxxxxx
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
