Re: [PERFORM] seqscan strikes again
Jim C. Nasby wrote: > 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): Can you post your configuration file ? I'd like to see for example your settings about: random_page_cost and effective_cache_size. Regards Gaetano Mendola ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] seqscan strikes again
Which column would you recommend? Did something stick out at you? On Tue, Nov 09, 2004 at 03:14:36PM -0800, Joshua D. Drake wrote: > > >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. > > Try increasing your statistics target for the column and then rerunning > analyze. > > Sincerely, > > Joshua D. Drake -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] 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?" ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] seqscan strikes again
"Jim C. Nasby" <[EMAIL PROTECTED]> writes:
> 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):
>-> 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)))
Can you alter the data representation? 7.4 doesn't have any stats about
functional indexes and so it's not likely to come up with a good number
about the selectivity of the index on ms_t(tick). It might be worth
materializing that value as a plain column and indexing the column.
(This being a join, I'm not sure it would help any, but it seems worth
trying.)
regards, tom lane
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] seqscan strikes again
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. Try increasing your statistics target for the column and then rerunning analyze. Sincerely, Joshua D. Drake -- Command Prompt, Inc., home of PostgreSQL Replication, and plPHP. Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL begin:vcard fn:Joshua D. Drake n:Drake;Joshua D. org:Command Prompt, Inc. adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl. x-mozilla-html:FALSE url:http://www.commandprompt.com/ version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] seqscan strikes again
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
