Partition performance causing ddl commands to slow down significantly
От | |
---|---|
Тема | Partition performance causing ddl commands to slow down significantly |
Дата | |
Msg-id | 20130412113133.5a830134ae84016b0174832fdc1a3173.55bd99cbf0.wbe@email11.secureserver.net обсуждение исходный текст |
Список | pgsql-bugs |
<span style=3D"font-family:Verdana; color:#000000; font-size:10= pt;">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 ass= ociated with the Parent table, and we have constraint_exclusion=3Dpartition= set. =0A =0AThe execution of the SQL query:&nbs= p; select count(*) from dna_strands; =0Ayields:  = ; &n= bsp;  = ; &n= bsp;  = ; QUERY PLAN_____________________________= ________________________________________________________________  = ; =0AAggregate (cost=3D2246778.49..2246778.50 rows=3D1 width=3D0= ) -> Append (0.00..2159647.04 rows=3D34852580 width=3D0)&n= bsp; -> Seq Scan on dna_strands (cost=3D0.00..0.00 row= s=3D1 width) Filt= er: (cid =3D 1) -> Index Scan using dna_stra= nds_y2003m01_cid on dna_strands_y2003m01 dna_strands (cost=3D0.00..677652 r= ows=3D1 width=3D0)  = ; Index Cond: (cid =3D 1) -> Index Sca= n using dna_strands_y2003m02_cid on dna_strands_y2003m02 dna_strands (cost= =3D0.00..974423 rows=3D1 width=3D0) = Index Cond: (cid =3D 1)  = ; -> Index Scan using dna_strands_y2003m03_cid on dna_strands_y2003m03 d= na_strands (cost=3D0.00..992301 rows=3D1 width=3D0) &n= bsp; Index Cond: (cid =3D 1) &= nbsp; ... ...=0A &n= bsp; ...=0A =0A &nb= sp; -> Index Scan using dna_strands_y2013m12_cid on dna_strands_y2013m12= dna_strands (cost=3D0.00..8.27 rows=3D1 width=3D0) &n= bsp; Index Cond: (cid =3D 1)=0A<d= iv>Question: Is there any way to modify the Planner to do the inverse of th= e Index Scan's. In other words, to start the index scans in reverse o= rder from the most recent date to the oldest date, i.e. "dna_strands_y2= 013m12" backwards. Our application users query much more heavily at the mos= t recent data that has been ingested into the PostgreSQL database. = ; Would this capability speed up query performance?=0AThanks</di= v>=0A
В списке pgsql-bugs по дате отправления: