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))
-> 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.
