Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running - Mailing list pgsql-performance

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=48loops=1) 
                                      Filter: ((record_update_date_time >= '2007-08-20 00:00:00'::timestamp without
timezone) 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=9064loops=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=48loops=1) 
                                      Filter: ((record_update_date_time >= '2007-08-20 00:00:00'::timestamp without
timezone) 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.