pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
seqscan strikes again
5+ messages / 4 participants
[nested] [flat]

* seqscan strikes again
@ 2004-11-09 22:23  Jim C. Nasby <decibel@decibel.org>
  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 23:14  Joshua D. Drake <jd@commandprompt.com>
  parent: 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 23:24  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Jim C. Nasby <decibel@decibel.org>
  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-10 17:00  Jim C. Nasby <decibel@decibel.org>
  parent: Joshua D. Drake <jd@commandprompt.com>
  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-11 07:58  Gaetano Mendola <mendola@bigfoot.com>
  parent: Jim C. Nasby <decibel@decibel.org>
  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