X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id EC86C3A3F85 for ; Tue, 9 Nov 2004 22:52:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 05806-09 for ; Tue, 9 Nov 2004 22:52:42 +0000 (GMT) Received: from flake.decibel.org (flake.decibel.org [66.143.173.58]) by svr1.postgresql.org (Postfix) with ESMTP id CE1593A3FF2 for ; Tue, 9 Nov 2004 22:52:42 +0000 (GMT) Received: by flake.decibel.org (Postfix, from userid 1001) id 42B091C8A6; Tue, 9 Nov 2004 22:23:45 +0000 (GMT) Date: Tue, 9 Nov 2004 16:23:45 -0600 From: "Jim C. Nasby" To: pgsql-performance@postgresql.org Subject: seqscan strikes again Message-ID: <20041109222345.GG46084@decibel.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline X-Operating-System: FreeBSD 4.10-RELEASE-p3 i386 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/161 X-Sequence-Number: 9115 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@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?"