<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