Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1626F9F9FF0 for ; Sun, 28 Oct 2007 12:22:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 82008-09 for ; Sun, 28 Oct 2007 12:22:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.249]) by postgresql.org (Postfix) with ESMTP id BB28D9FA01C for ; Sun, 28 Oct 2007 12:22:53 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id b33so182724ana for ; Sun, 28 Oct 2007 08:22:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:mime-version:content-type; bh=Q4m9ECScfl9Q4WFr2lcnxxMmqRGJxYvRKucb/B7Qpgc=; b=OoYioKzkiZsDKruTiRq1hkfDZoxHEhdov8MLd2ZlCoOQdwmnv5sSJYLLUXQLAxAcZFAjVN+A+0vgzSwX/hF8Q4Xw1/VIqyw9tbeh0zTNdRsAiwy4mu/5SJ4j900yhLu4s/dhYc2V0mM+PtnnlJ9+sRHW+8m107j1lvgqTdj1/pk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:mime-version:content-type; b=hxBY0WtmcKZQS7zIeONKUS7MgA2NZ/yvbSEm0HQ+WCGXEPrjmFhXkTex/OnYDtejjb1SkmWpl9S+LHZHUw9/vxwWm//nDounnzb4Na1tGJUFsfWn1rKYnE1aGkpuFbwyoRmtrwuUHehE727QoG7Q7YPpYg9D26tqG6XIdplBl2o= Received: by 10.100.225.19 with SMTP id x19mr172725ang.1193584973163; Sun, 28 Oct 2007 08:22:53 -0700 (PDT) Received: by 10.100.215.8 with HTTP; Sun, 28 Oct 2007 08:22:53 -0700 (PDT) Message-ID: <965aa3ed0710280822x4d1ffc9dh74f67740fdcff5cb@mail.gmail.com> Date: Sun, 28 Oct 2007 20:52:53 +0530 From: "Nimesh Satam" To: pgsql-performance@postgresql.org, nimesh@zedo.com Subject: Append Cost in query planners MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_776_30875164.1193584973157" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=0 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200710/330 X-Sequence-Number: 27352 ------=_Part_776_30875164.1193584973157 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline All, We are trying to implement partition on one tables on date basis. the overall cost and timming and cost of the query is increasing on the Append of the child table output. As shown below: *-> Append (cost=0.00..112217.92 rows=2752906 width=52) (actual time= 2454.207..20712.021 rows=2752905 loops=1) -> Seq Scan on trm (cost=0.00..28570.35 rows=1 width=52) (actual time= 2423.374..2423.374 rows=0 loops=1) Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone)) -> Seq Scan on trm_d20070601 trm (cost=0.00..29203.41 rows=961094 width=52) (actual time=30.825..3027.217 rows=961094 loops=1) Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone)) -> Seq Scan on trm_d20070602 trm (cost=0.00..27442.52 rows=903168 width=52) (actual time=11.142..2687.422 rows=903168 loops=1) Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone)) -> Seq Scan on trm_d20070603 trm (cost=0.00..27001.64 rows=888643 width=52) (actual time=13.697..2568.012 rows=888643 loops=1) Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone))* Can someone let me know, how we can reduce the overall cost and time of the append operation, and what parameters in the confirguration needs to be changed? Let me know if you need any further information to improve the query plan. Regards, Nimesh. ------=_Part_776_30875164.1193584973157 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
All,
 
We are trying to implement partition on one tables on date basis. the overall cost and timming and cost of the query is increasing on the Append of the child table output. As shown below:
 
->  Append  (cost=0.00..112217.92 rows=2752906 width=52) (actual time=2454.207..20712.021 rows=2752905 loops=1)
 ->  Seq Scan on trm  (cost=0.00..28570.35 rows=1 width=52) (actual time=2423.374..2423.374 rows=0 loops=1)
  Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone))
 ->  Seq Scan on trm_d20070601 trm  (cost= 0.00..29203.41 rows=961094 width=52) (actual time=30.825..3027.217 rows=961094 loops=1)
  Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone))
 ->  Seq Scan on trm_d20070602 trm  (cost=0.00..27442.52 rows=903168 width=52) (actual time=11.142..2687.422 rows=903168 loops=1)
  Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone))
 ->  Seq Scan on trm_d20070603 trm  (cost=0.00..27001.64 rows=888643 width=52) (actual time=13.697..2568.012 rows=888643 loops=1)
  Filter: ((sqldate >= '2007-06-01 00:00:00'::timestamp without time zone) AND (sqldate <= '2007-06-03 00:00:00'::timestamp without time zone))
 
 
Can someone let me know, how we can reduce the overall cost and time of the append operation, and what parameters in the confirguration needs to be changed?
 
 
Let me know if you need any further information to improve the query plan.
 
 
Regards,
Nimesh.
------=_Part_776_30875164.1193584973157--