Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 75C6A9F998F for ; Tue, 11 Sep 2007 23:49:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 89511-06 for ; Tue, 11 Sep 2007 23:49:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id 8992B9F9EF9 for ; Tue, 11 Sep 2007 23:49:15 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id u77so133272pyb for ; Tue, 11 Sep 2007 19:49:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:subject:from:to:cc:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding; bh=Ni8R4w5OxVc7eGadhYuglnJMZRaCVkp7irifEi3ZwSg=; b=gfmQ/9mXSc7a64GL1LcGGS8QD3l3sue2oAypCQp3tVa9FOQ7vFqT0FD1OejkF8UiggD8k6ttow/LaBwQxv+96ga09bGvPCd2GEB6ODw1Ti1SolZhp2TNJ9Opn8iH5xkZg0ra119cwYj+svsADE+1EUf6gkX/1UvYTStrqLSNlnM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:subject:from:to:cc:in-reply-to:references:content-type:date:message-id:mime-version:x-mailer:content-transfer-encoding; b=TDBJAfOp2ljuiydwQ/GZ6oL7fk+Q3FAiJCs7Lrl8gntlLFE1uZpdw0zM9Hf+MREDcxGgQShIMuiXKKmE/CsnU7yff0/XWfDLHm8Ro69xeGxBbHaXLfB/5dnFiMP++lhpWQ7VTOLRcUklXzVvK5tqzO1q6uryXGBI35twXxx2eoc= Received: by 10.35.41.8 with SMTP id t8mr8221687pyj.1189565353475; Tue, 11 Sep 2007 19:49:13 -0700 (PDT) Received: from ?202.146.86.77? ( [202.146.86.77]) by mx.google.com with ESMTPS id a79sm11549936pye.2007.09.11.19.49.10 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 11 Sep 2007 19:49:12 -0700 (PDT) Subject: Re: Re: 500rows = 1min/2.5k rows=20min/6K rows 2 hours and still running From: El-Lotso To: Tom Lane Cc: pgsql-performance@postgresql.org 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> Content-Type: text/plain Date: Wed, 12 Sep 2007 10:48:28 +0800 Message-Id: <1189565308.32450.15.camel@neuromancer.home.net> Mime-Version: 1.0 X-Mailer: Evolution 2.10.2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200709/216 X-Sequence-Number: 26761 X-No-Archive: yes 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.