pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedseqscan strikes again
5+ messages / 4 participants
[nested] [flat]
* seqscan strikes again
@ 2004-11-09 22:23 Jim C. Nasby <decibel@decibel.org>
2004-11-09 23:14 ` Re: seqscan strikes again Joshua D. Drake <jd@commandprompt.com>
2004-11-09 23:24 ` Re: seqscan strikes again Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 07:58 ` Re: seqscan strikes again Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 3 replies; 5+ messages in thread
From: Jim C. Nasby @ 2004-11-09 22:23 UTC (permalink / raw)
To: 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) (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?"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: seqscan strikes again
2004-11-09 22:23 seqscan strikes again Jim C. Nasby <decibel@decibel.org>
@ 2004-11-09 23:14 ` Joshua D. Drake <jd@commandprompt.com>
2004-11-10 17:00 ` Re: seqscan strikes again Jim C. Nasby <decibel@decibel.org>
2 siblings, 1 reply; 5+ messages in thread
From: Joshua D. Drake @ 2004-11-09 23:14 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-performance
> 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 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL
Attachments:
jd.vcf (text/x-vcard, 638B) download
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:jd@commandprompt.com
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: seqscan strikes again
2004-11-09 22:23 seqscan strikes again Jim C. Nasby <decibel@decibel.org>
2004-11-09 23:14 ` Re: seqscan strikes again Joshua D. Drake <jd@commandprompt.com>
@ 2004-11-10 17:00 ` Jim C. Nasby <decibel@decibel.org>
0 siblings, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2004-11-10 17:00 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-performance
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 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?"
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: seqscan strikes again
2004-11-09 22:23 seqscan strikes again Jim C. Nasby <decibel@decibel.org>
@ 2004-11-09 23:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2004-11-09 23:24 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>; +Cc: pgsql-performance
"Jim C. Nasby" <decibel@decibel.org> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: seqscan strikes again
2004-11-09 22:23 seqscan strikes again Jim C. Nasby <decibel@decibel.org>
@ 2004-11-11 07:58 ` Gaetano Mendola <mendola@bigfoot.com>
2 siblings, 0 replies; 5+ messages in thread
From: Gaetano Mendola @ 2004-11-11 07:58 UTC (permalink / raw)
To: Jim C. Nasby <decibel@decibel.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-11-11 07:58 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-09 22:23 seqscan strikes again Jim C. Nasby <decibel@decibel.org>
2004-11-09 23:14 ` Joshua D. Drake <jd@commandprompt.com>
2004-11-10 17:00 ` Jim C. Nasby <decibel@decibel.org>
2004-11-09 23:24 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-11-11 07:58 ` Gaetano Mendola <mendola@bigfoot.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox