Re: Partition performance causing ddl commands to slow down significantly - Mailing list pgsql-bugs
| From | |
|---|---|
| Subject | Re: Partition performance causing ddl commands to slow down significantly |
| Date | |
| Msg-id | 20130412114630.5a830134ae84016b0174832fdc1a3173.fc7948457c.wbe@email11.secureserver.net Whole thread Raw |
| Responses |
Re: Partition performance causing ddl commands to slow down
significantly
(John R Pierce <pierce@hogranch.com>)
|
| List | pgsql-bugs |
<span style=3D"font-family:Verdana; color:#000000; font-size:10= pt;">Sorry, Is this visible? =0A =0AWe 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 Ja= n 2003 thru Dec 2013. We have 268 child partitions associated with the = Parent table, and we have constraint_exclusion=3Dpartition set. =0A<d= iv>The execution of the SQL query: select count(*) from dna_strands; = =0Ayields: QUERY PLAN_________________________________= ____________________________________________________________ </= div>=0AAggregate (cost=3D2246778.49..2246778.50 rows=3D1 width=3D0)<BR= > -> Append (0.00..2159647.04 rows=3D34852580 width=3D0) = -> Seq Scan on dna_strands (cost=3D0.00..0.00 rows=3D= 1 width) Filter: = (cid =3D 1) -> Index Scan using dna_strands_= y2003m01_cid on dna_strands_y2003m01 dna_strands (cost=3D0.00..677652 rows= =3D1 width=3D0) &n= bsp; Index Cond: (cid =3D 1) -> Index Scan u= sing dna_strands_y2003m02_cid on dna_strands_y2003m02 dna_strands (cost=3D0= .00..974423 rows=3D1 width=3D0) &nbs= p; Index Cond: (cid =3D 1) -&= gt; Index Scan using dna_strands_y2003m03_cid on dna_strands_y2003m03 dna_s= trands (cost=3D0.00..992301 rows=3D1 width=3D0) = Index Cond: (cid =3D 1) = ; ... ...=0A = -> Index Scan using dna_strands_y2013m12_cid on dna_strands= _y2013m12 dna_strands (cost=3D0.00..8.27 rows=3D1 width=3D0) = ; Index Cond: (cid =3D 1)</= div>=0AQuestion: Is there any way to modify the Planner to do the inve= rse 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_s= trands_y2013m12" backwards. Our application users query much more heavily a= t the most recent data that has been ingested into the PostgreSQL datab= ase. Would this capability speed up query performance?=0A&= nbsp;=0AThanks=0A =0A = =0A<BLOCKQUOTE style=3D"BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FON= T-FAMILY: verdana; COLOR: black; MARGIN-LEFT: 8px; FONT-SIZE: 10pt" id=3Dre= plyBlockquote webmail=3D"1">=0A-------- Original M= essage --------Subject: Re: [BUGS] Partition performance causing ddl co= mmands to slowdown significantlyFrom: Andres Freund <<a href=3D"= mailto:andres@2ndquadrant.com">andres@2ndquadrant.com>Date: Fri,= April 12, 2013 11:36 amTo: <a href=3D"mailto:fburgess@radiantblue.com"= >fburgess@radiantblue.comHi,On 2013-04-12 11:31:33 -070= 0, fburgess@radiantblue.com= wrote:> <html><body><span style=3D"font-family:Verda= na; color:#000000; font-size:10pt;"><div>We are having performance= related problems on one of our big data Partition tables. The table is par= titioned by date and the partitions are organized from Jan 2003 thru Dec 20= 13. <BR>We have 268 child partitions associated with the Parent table= , and we have constraint_exclusion=3Dpartition set. </div>> &l= t;div> </div>> <div>The execution of the SQL= query: select count(*) from dna_strands; </div>> &l= t;div>yields:  = ; &= ;nbsp;  = ; &= ;nbsp;  = ; &= ;nbsp;  = ; QUE= RY PLAN<BR>__________________________________________________________= ___________________________________ </div>>= ; <div>Aggregate (cost=3D2246778.49..2246778.50 rows=3D1 width=3D0)&l= t;BR> -> Append (0.00..2159647.04 rows=3D34852580 width= =3D0)<BR> -> Seq Scan = on dna_strands (cost=3D0.00..0.00 rows=3D1 width)<BR> &n= bsp; = Filter: (cid =3D 1)<BR> -&= ;gt; Index Scan using dna_strands_y2003m01_cid on dna_strands_y2003m01 dna_= strands (cost=3D0.00..677652 rows=3D1 width=3D0)<BR> &nb= sp; &a= mp;nbsp; Index Cond: (cid =3D 1)<BR> &am= p;nbsp; -> Index Scan using dna_strands_y2003m02_cid on dna_strands_= y2003m02 dna_strands (cost=3D0.00..974423 rows=3D1 width=3D0)<BR>&= ;nbsp;  = ; Index Cond: (cid =3D 1)<BR> = -> Index Scan using dna_strands_y2003m03_cid on= dna_strands_y2003m03 dna_strands (cost=3D0.00..992301 rows=3D1 width=3D0)&= lt;BR> &n= bsp; Index Cond: (cid =3D 1)<BR>&nb= sp; ...<BR> &nbs= p; ...</div>> <div> &nb= sp; ...</div>> <div> </div>= > <div> -> Index Sc= an using dna_strands_y2013m12_cid on dna_strands_y2013m12 dna_strands (cost= =3D0.00..8.27 rows=3D1 width=3D0)<BR> &a= mp;nbsp; Index = Cond: (cid =3D 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 m= ost 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 t= his capability speed up query performance?</div>> <div>T= hanks</div>> <div><BR> </div></s= pan></body></html>Youre sending completely unreadabl= e html only mails again.Greetings,Andres Freund-- <= BR>Andres Freund http://www.2ndQuadr= ant.com/PostgreSQL Development, 24x7 Support, Training & Servic= es
pgsql-bugs by date: