DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=O4vbtubyQWr/4bJ4LLiV4SA7pUbSarNen765yKPMDHgZnc4MXDyh3Yu3u5Su1Vu5bNdmTJU12sYy7ONP5vSjZdT+40X8IL5WdA/d1rhKW1kAGdrKuQQbpC5sWde6tDTlqTcxbsMZvh8xZioSc3X5NmObn4Hv2RmueLYRMfZpQsk=
Message-ID: <4724ACE7.4090401@enterprisedb.com>
Date: Sun, 28 Oct 2007 15:38:15 +0000
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: Nimesh Satam <nimesh.zedo@gmail.com>
CC: pgsql-performance@postgresql.org,  nimesh@zedo.com
Subject: Re: Append Cost in query planners
References: <965aa3ed0710280822x4d1ffc9dh74f67740fdcff5cb@mail.gmail.com>
In-Reply-To: <965aa3ed0710280822x4d1ffc9dh74f67740fdcff5cb@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Nimesh Satam wrote:
> 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?

Does the query really return almost 3 million rows? If that's the case,
I'm afraid there isn't much you can do, software-wise. If not, show us
the complete query and EXPLAIN ANALYZE output.

-- 
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com