Re: Partition performance causing ddl commands to slow down significantly - Mailing list pgsql-bugs

<span style=3D"font-family:Verdana; color:#000000; font-size:10=
pt;">Hi John,=0A =0AThe SQL query should be=
: select count(*) from dna_strands where cid =3D 1;=0A </di=
v>=0AI  just realize don't think this is not going to work. if fo=
r the sake of argument that cid =3D 1 is much more likely be be found =
in a more recent partition, any inverse search mechanism in the planne=
r will find that match first but then continue through all of the other par=
titions, Right? The only way to optimize this is to find some way of adding=
 the partition key date to the where clause.=0A =0A<d=
iv>In actuality the query's are being generated via Hibernate=0A=
 =0Athanks=0A<BLOCKQUOTE style=3D"BORDER-LEFT: b=
lue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGI=
N-LEFT: 8px; FONT-SIZE: 10pt" id=3DreplyBlockquote webmail=3D"1">=0A<DIV id=
=3DwmQuoteWrapper>-------- Original Message --------Subject: Re: [BUGS]=
 Partition performance causing ddl commands to slowdown significantly<B=
R>From: John R Pierce <pierce@hog=
ranch.com>Date: Fri, April 12, 2013 11:53 amTo: <a href=3D"m=
ailto:pgsql-bugs@postgresql.org">pgsql-bugs@postgresql.org=0A<D=
IV class=3Dmoz-cite-prefix>On 4/12/2013 11:46 AM, <A class=3Dmoz-txt-link-a=
bbreviated href=3D"mailto:fburgess@radiantblue.com" target=3D_blank>fburges=
s@radiantblue.com wrote:=0A<BLOCKQUOTE cite=3Dmid:201304=
12114630.5a830134ae84016b0174832fdc1a3173.fc7948457c.wbe@email11.secureserv=
er.net type=3D"cite"><SPAN style=3D"FONT-FAMILY: Verdana; COLOR: #000000; F=
ONT-SIZE: 10pt">=0AWe are having performance related problems on one o=
f our big data Partition tables. The table is partitioned by date and the p=
artitions are organized from Jan 2003 thru Dec 2013. We have 268 child =
partitions associated with the Parent table, and we have constraint_exclusi=
on=3Dpartition set. =0AThe execution of the SQL query:  sel=
ect count(*) from dna_strands; =0Ayields:  QUERY PLAN__=
___________________________________________________________________________=
________________   =0AAggregate (cost=3D2246778.49..22=
46778.50 rows=3D1 width=3D0)  -> Append (0.00..2159647.04 rows=
=3D34852580 width=3D0)     -> Seq Scan on dna_st=
rands (cost=3D0.00..0.00 rows=3D1 width)     &=
nbsp;    Filter: (cid =3D 1)     -&g=
t; Index Scan using dna_strands_y2003m01_cid on dna_strands_y2003m01 dna_st=
rands (cost=3D0.00..677652 rows=3D1 width=3D0)    &=
nbsp;      Index Cond: (cid =3D 1)  =
   -> Index Scan using dna_strands_y2003m02_cid on dna_strands=
_y2003m02 dna_strands (cost=3D0.00..974423 rows=3D1 width=3D0) &nb=
sp;         Index Cond: (cid =3D 1)=
     -> Index Scan using dna_strands_y2003m03_ci=
d on dna_strands_y2003m03 dna_strands (cost=3D0.00..992301 rows=3D1 width=
=3D0)           Index=
 Cond: (cid =3D 1)     ...   &nb=
sp; ...=0A     -> Index Scan using dna_st=
rands_y2013m12_cid on dna_strands_y2013m12 dna_strands (cost=3D0.00..8.27 r=
ows=3D1 width=3D0)         =
;  Index Cond: (cid =3D 1)=0AQuestion: Is there any way to =
modify the Planner to do the inverse of the Index Scan's.  In other wo=
rds, to start the index scans in reverse order from the most recent dat=
e to the oldest date, i.e. "dna_strands_y2013m12" backwards. Our applicatio=
n users query much more heavily at the most recent data that has been i=
ngested into the PostgreSQL database.  Would this capability speed up =
query performance?it wouldn't speed up your e=
xample, as your example has to scan every single row of the whole mess.&nbs=
p;  not sure where Filter: (cid=3D1) comes from, since you showed the =
query as SELECT COUNT(*) FROM dna_strands;<PRE class=3Dmoz-sign=
ature cols=3D"72">-- john r pierce 37N 122W somewhere on the middle of the =
left coast