Skip site navigation (1) Skip section navigation (2)

Append Cost in query planners

From: "Nimesh Satam" <nimesh(dot)zedo(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org, nimesh(at)zedo(dot)com
Subject: Append Cost in query planners
Date: 2007-10-28 15:22:53
Message-ID: 965aa3ed0710280822x4d1ffc9dh74f67740fdcff5cb@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

Responses

pgsql-performance by date

Next:From: Heikki LinnakangasDate: 2007-10-28 15:38:15
Subject: Re: Append Cost in query planners
Previous:From: Simon RiggsDate: 2007-10-28 09:13:53
Subject: Re: partitioned table and ORDER BY indexed_fieldDESCLIMIT 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group