seqscan strikes again - Mailing list pgsql-performance

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)
(actualtime=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.030rows=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.368rows=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)
(actualtime=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@decibel.org
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?"