Re: [BUGS] Partition performance causing ddl commands to slow down significantly

fburgess Fri, 12 Apr 2013 11:47:02 -0700

Sorry, Is this visible?

We are having performance related problems on one of our big data Partition tables. The table is partitioned by date and the partitions are organized from Jan 2003 thru Dec 2013.
We have 268 child partitions associated with the Parent table, and we have constraint_exclusion=partition set.

The execution of the SQL query: select count(*) from dna_strands;

yields: QUERY PLAN
_____________________________________________________________________________________________

Aggregate (cost=2246778.49..2246778.50 rows=1 width=0)
-> Append (0.00..2159647.04 rows=34852580 width=0)
     -> Seq Scan on dna_strands (cost=0.00..0.00 rows=1 width)
          Filter: (cid = 1)
     -> Index Scan using dna_strands_y2003m01_cid on dna_strands_y2003m01 dna_strands (cost=0.00..677652 rows=1 width=0)
           Index Cond: (cid = 1)
     -> Index Scan using dna_strands_y2003m02_cid on dna_strands_y2003m02 dna_strands (cost=0.00..974423 rows=1 width=0)
           Index Cond: (cid = 1)
     -> Index Scan using dna_strands_y2003m03_cid on dna_strands_y2003m03 dna_strands (cost=0.00..992301 rows=1 width=0)
           Index Cond: (cid = 1)
     ...
     ...

-> Index Scan using dna_strands_y2013m12_cid on dna_strands_y2013m12 dna_strands (cost=0.00..8.27 rows=1 width=0)
Index Cond: (cid = 1)

Question: Is there any way to modify the Planner to do the inverse of the Index Scan's. In other words, to start the index scans in reverse order from
the most recent date to the oldest date, i.e. "dna_strands_y2013m12" backwards. Our application users query much more heavily at the most recent data that
has been ingested into the PostgreSQL database. Would this capability speed up query performance?

Thanks

-------- Original Message --------
Subject: Re: [BUGS] Partition performance causing ddl commands to slow
down significantly
From: Andres Freund <and...@2ndquadrant.com>
Date: Fri, April 12, 2013 11:36 am
To: fburg...@radiantblue.com

Hi,

On 2013-04-12 11:31:33 -0700, fburg...@radiantblue.com wrote:
> <html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>We are having performance related problems on one of our big data Partition tables. The table is partitioned by date and the partitions are organized from Jan 2003 thru Dec 2013. <BR>We have 268 child partitions associated with the Parent table, and we have constraint_exclusion=partition set. </div>
> <div> </div>
> <div>The execution of the SQL query:  select count(*) from dna_strands; </div>
> <div>yields:                                                           QUERY PLAN<BR>_____________________________________________________________________________________________   </div>
> <div>Aggregate (cost=2246778.49..2246778.50 rows=1 width=0)<BR>  -> Append (0.00..2159647.04 rows=34852580 width=0)<BR>     -> Seq Scan on dna_strands (cost=0.00..0.00 rows=1 width)<BR>          Filter: (cid = 1)<BR>     -> Index Scan using dna_strands_y2003m01_cid on dna_strands_y2003m01 dna_strands (cost=0.00..677652 rows=1 width=0)<BR>           Index Cond: (cid = 1)<BR>     -> Index Scan using dna_strands_y2003m02_cid on dna_strands_y2003m02 dna_strands (cost=0.00..974423 rows=1 width=0)<BR>           Index Cond: (cid = 1)<BR>     -> Index Scan using dna_strands_y2003m03_cid on dna_strands_y2003m03 dna_strands (cost=0.00..992301 rows=1 width=0)<BR>           Index Cond: (cid = 1)<BR>     ...<BR>     ...</div>
> <div>     ...</div>
> <div> </div>
> <div>     -> Index Scan using dna_strands_y2013m12_cid on dna_strands_y2013m12 dna_strands (cost=0.00..8.27 rows=1 width=0)<BR>           Index Cond: (cid = 1)</div>
> <div>Question: Is there any way to modify the Planner to do the inverse of the Index Scan's.  In other words, to start the index scans in reverse order from <BR>the most recent date to the oldest date, i.e. "dna_strands_y2013m12" backwards. Our application users query much more heavily at the most recent data that <BR>has been ingested into the PostgreSQL database.  Would this capability speed up query performance?</div>
> <div>Thanks</div>
> <div><BR> </div></span></body></html>

Youre sending completely unreadable html only mails again.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Re: [BUGS] Partition performance causing ddl commands to slow down significantly

Reply via email to