<html><body><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>Sorry, Is this visible? </div>
<div> </div>
<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>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> -> 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> </div>
<div>Thanks</div>
<div><BR> </div>
<div> </div>
<BLOCKQUOTE style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=replyBlockquote webmail="1">
<DIV id=wmQuoteWrapper>-------- Original Message --------<BR>Subject: Re: [BUGS] Partition performance causing ddl commands to slow<BR>down significantly<BR>From: Andres Freund <<a href="mailto:andres(at)2ndquadrant(dot)com">andres(at)2ndquadrant(dot)com</a>><BR>Date: Fri, April 12, 2013 11:36 am<BR>To: <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a><BR><BR>Hi,<BR><BR>On 2013-04-12 11:31:33 -0700, <a href="mailto:fburgess(at)radiantblue(dot)com">fburgess(at)radiantblue(dot)com</a> wrote:<BR>> <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><BR>> <div>&nbsp;</div><BR>> <div>The execution of the SQL query:&nbsp; select count(*) from dna_strands; </div><BR>> <div>yields:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; QUERY PLAN<BR>_____________________________________________________________________________________________&nbsp;&nbsp; </div><BR>> <div>Aggregate (cost=2246778.49..2246778.50 rows=1 width=0)<BR>&nbsp; -&gt; Append (0.00..2159647.04 rows=34852580 width=0)<BR>&nbsp;&nbsp;&nbsp;&nbsp; -&gt; Seq Scan on dna_strands (cost=0.00..0.00 rows=1 width)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Filter: (cid = 1)<BR>&nbsp;&nbsp;&nbsp;&nbsp; -&gt; Index Scan using dna_strands_y2003m01_cid on dna_strands_y2003m01 dna_strands (cost=0.00..677652 rows=1 width=0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (cid = 1)<BR>&nbsp;&nbsp;&nbsp;&nbsp; -&gt; Index Scan using dna_strands_y2003m02_cid on dna_strands_y2003m02 dna_strands (cost=0.00..974423 rows=1 width=0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (cid = 1)<BR>&nbsp;&nbsp;&nbsp;&nbsp; -&gt; Index Scan using dna_strands_y2003m03_cid on dna_strands_y2003m03 dna_strands (cost=0.00..992301 rows=1 width=0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (cid = 1)<BR>&nbsp;&nbsp;&nbsp;&nbsp; ...<BR>&nbsp;&nbsp;&nbsp;&nbsp; ...</div><BR>> <div>&nbsp;&nbsp;&nbsp;&nbsp; ...</div><BR>> <div>&nbsp;</div><BR>> <div>&nbsp;&nbsp;&nbsp;&nbsp; -&gt; Index Scan using dna_strands_y2013m12_cid on dna_strands_y2013m12 dna_strands (cost=0.00..8.27 rows=1 width=0)<BR>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Index Cond: (cid = 1)</div><BR>> <div>Question: Is there any way to modify the Planner to do the inverse of the Index Scan's.&nbsp; 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.&nbsp; Would this capability speed up query performance?</div><BR>> <div>Thanks</div><BR>> <div><BR>&nbsp;</div></span></body></html><BR><BR>Youre sending completely unreadable html only mails again.<BR><BR>Greetings,<BR><BR>Andres Freund<BR><BR>-- <BR>Andres Freund <a href="http://www.2ndQuadrant.com">http://www.2ndQuadrant.com</a>/<BR>PostgreSQL Development, 24x7 Support, Training & Services<BR></DIV></BLOCKQUOTE></span></body></html>