pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: El-Lotso <el.lotso@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
Date: Wed, 12 Sep 2007 10:48:28 +0800
Message-ID: <1189565308.32450.15.camel@neuromancer.home.net> (raw)
In-Reply-To: <1189563332.32450.11.camel@neuromancer.home.net>
References: <1189529868.17184.17.camel@neuromancer.home.net>
	<1189530143.17184.21.camel@neuromancer.home.net>
	<15496.1189535000@sss.pgh.pa.us>
	<1189563332.32450.11.camel@neuromancer.home.net>

On Wed, 2007-09-12 at 10:15 +0800, El-Lotso wrote:
> I'm downgrading to 8.1.9 to see if it helps too.\

Nope : Doesn't help at all.. the number of rows at the nested loop and
hash joins are still 1 to 500 ratio. This plan is slightly different in
that PG is choosing seq_scans

Nested Loop Left Join  (cost=2604.28..4135.15 rows=1 width=59) (actual time=249.973..15778.157 rows=528 loops=1)
  Join Filter: ((("inner".id)::text = ("outer".id)::text) AND ("inner".hid = "outer".hid) AND ("inner".seq_time = "outer".seq_time) AND ("inner".seq_date = "outer".seq_date))
  ->  Nested Loop Left Join  (cost=1400.08..2766.23 rows=1 width=67) (actual time=168.375..8002.573 rows=528 loops=1)
        Join Filter: ((("inner".id)::text = ("outer".id)::text) AND ("inner".hid = "outer".hid) AND ("inner".seq_time = "outer".seq_time) AND ("inner".seq_date = "outer".seq_date))
        ->  Hash Join  (cost=127.25..1328.68 rows=1 width=59) (actual time=74.195..84.855 rows=528 loops=1)
              Hash Cond: ((("outer".id)::text = ("inner".id)::text) AND ("outer".ttype = "inner".ttype) AND ("outer".start_timestamp = "inner".start_timestamp))
              ->  Seq Scan on trh  (cost=0.00..1060.18 rows=9416 width=36) (actual time=0.022..53.830 rows=9416 loops=1)
                    Filter: ((ttype = 35) OR (ttype = 75) OR (ttype = 703) OR (ttype = 740) OR (ttype = 764))
              ->  Hash  (cost=125.53..125.53 rows=230 width=63) (actual time=12.487..12.487 rows=192 loops=1)
                    ->  Hash Join  (cost=18.69..125.53 rows=230 width=63) (actual time=11.043..12.007 rows=192 loops=1)
                          Hash Cond: (("outer".id)::text = ("inner".id)::text)
                          ->  Seq Scan on ts  (cost=0.00..87.36 rows=3436 width=40) (actual time=0.003..5.436 rows=3436 loops=1)
                          ->  Hash  (cost=18.57..18.57 rows=48 width=23) (actual time=0.876..0.876 rows=48 loops=1)
                                ->  Seq Scan on d  (cost=0.00..18.57 rows=48 width=23) (actual time=0.019..0.771 rows=48 loops=1)
                                      Filter: ((record_update_date_time >= '2007-08-20 00:00:00'::timestamp without time zone) AND (record_update_date_time <= '2007-09-08 00:00:00'::timestamp without time zone) AND ((code)::text = 'HUA75'::text))
        ->  Hash Join  (cost=1272.83..1437.52 rows=1 width=61) (actual time=11.784..14.216 rows=504 loops=528)
              Hash Cond: ((("outer".id)::text = ("inner".id)::text) AND ("outer".ttype = "inner".ttype) AND ("outer".start_timestamp = "inner".start_timestamp))
              ->  Seq Scan on ts  (cost=0.00..87.36 rows=3436 width=40) (actual time=0.003..5.744 rows=3436 loops=528)
              ->  Hash  (cost=1268.29..1268.29 rows=606 width=59) (actual time=82.783..82.783 rows=504 loops=1)
                    ->  Hash Join  (cost=18.69..1268.29 rows=606 width=59) (actual time=76.454..81.515 rows=504 loops=1)
                          Hash Cond: (("outer".id)::text = ("inner".id)::text)
                          ->  Seq Scan on trh  (cost=0.00..1198.22 rows=9064 width=36) (actual time=0.051..66.555 rows=9064 loops=1)
                                Filter: ((ttype = 69) OR (ttype = 178) OR (ttype = 198) OR (ttype = 704) OR (ttype = 757) OR (ttype = 741) OR (ttype = 765))
                          ->  Hash  (cost=18.57..18.57 rows=48 width=23) (actual time=0.863..0.863 rows=48 loops=1)
                                ->  Seq Scan on d  (cost=0.00..18.57 rows=48 width=23) (actual time=0.019..0.761 rows=48 loops=1)
                                      Filter: ((record_update_date_time >= '2007-08-20 00:00:00'::timestamp without time zone) AND (record_update_date_time <= '2007-09-08 00:00:00'::timestamp without time zone) AND ((code)::text = 'HUA75'::text))
  ->  Hash Join  (cost=1204.20..1368.89 rows=1 width=61) (actual time=11.498..13.941 rows=504 loops=528)
        Hash Cond: ((("outer".id)::text = ("inner".id)::text) AND ("outer".ttype = "inner".ttype) AND ("outer".start_timestamp = "inner".start_timestamp))
        ->  Seq Scan on ts  (cost=0.00..87.36 rows=3436 width=40) (actual time=0.003..5.593 rows=3436 loops=528)
        ->  Hash  (cost=1199.62..1199.62 rows=610 width=59) (actual time=70.186..70.186 rows=504 loops=1)
              ->  Hash Join  (cost=18.69..1199.62 rows=610 width=59) (actual time=64.270..68.886 rows=504 loops=1)
                    Hash Cond: (("outer".id)::text = ("inner".id)::text)
                    ->  Seq Scan on trh  (cost=0.00..1129.20 rows=9128 width=36) (actual time=0.020..54.050 rows=9128 loops=1)
                          Filter: ((ttype = 177) OR (ttype = 197) OR (ttype = 705) OR (ttype = 742) OR (ttype = 758) OR (ttype = 766))
                    ->  Hash  (cost=18.57..18.57 rows=48 width=23) (actual time=1.100..1.100 rows=48 loops=1)
                          ->  Seq Scan on d  (cost=0.00..18.57 rows=48 width=23) (actual time=0.019..0.994 rows=48 loops=1)
                                Filter: ((record_update_date_time >= '2007-08-20 00:00:00'::timestamp without time zone) AND (record_update_date_time <= '2007-09-08 00:00:00'::timestamp without time zone) AND ((code)::text = 'HUA75'::text))
Total runtime: 15779.769 ms

Am I screwed? Is a schema redesign really a necessity? This would be a
real pain given the rewrite of _all_ the queries and can't maintain
compatibility in the front-end app between sql server and PG.




view thread (10+ messages)  latest in thread

Message-ID: <1189565308.32450.15.camel@neuromancer.home.net>
Permalink:  ../1189565308.32450.15.camel@neuromancer.home.net/
Also on:    postgresql.org/message-id/1189565308.32450.15.camel@neuromancer.home.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: el.lotso@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running
  In-Reply-To: <1189565308.32450.15.camel@neuromancer.home.net>
  References: <1189529868.17184.17.camel@neuromancer.home.net> <1189530143.17184.21.camel@neuromancer.home.net> <15496.1189535000@sss.pgh.pa.us> <1189563332.32450.11.camel@neuromancer.home.net> <1189565308.32450.15.camel@neuromancer.home.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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