Re: Partition performance causing ddl commands to slow down significantly

On 4/12/2013 11:46 AM, fburgess(at)radiantblue(dot)com wrote:
> 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 associated with the Parent table, and we
> have constraint_exclusion=partition set.
> The execution of the SQL query: select count(*) from dna_strands;
> yields: QUERY PLAN
> _____________________________________________________________________________________________
>
> Aggregate (cost=2246778.49..2246778.50 rows=1 width=0)
> -> Append (0.00..2159647.04 rows=34852580 width=0)
> -> Seq Scan on dna_strands (cost=0.00..0.00 rows=1 width)
> Filter: (cid = 1)
> -> Index Scan using dna_strands_y2003m01_cid on
> dna_strands_y2003m01 dna_strands (cost=0.00..677652 rows=1 width=0)
> Index Cond: (cid = 1)
> -> Index Scan using dna_strands_y2003m02_cid on
> dna_strands_y2003m02 dna_strands (cost=0.00..974423 rows=1 width=0)
> Index Cond: (cid = 1)
> -> Index Scan using dna_strands_y2003m03_cid on
> dna_strands_y2003m03 dna_strands (cost=0.00..992301 rows=1 width=0)
> Index Cond: (cid = 1)
> ...
> ...
> -> Index Scan using dna_strands_y2013m12_cid on
> dna_strands_y2013m12 dna_strands (cost=0.00..8.27 rows=1 width=0)
> Index Cond: (cid = 1)
> 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
> 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
> has been ingested into the PostgreSQL database. Would this capability
> speed up query performance?

it wouldn't speed up your example, as your example has to scan every
single row of the whole mess. not sure where Filter: (cid=1) comes
from, since you showed the query as SELECT COUNT(*) FROM dna_strands;

--
john r pierce 37N 122W
somewhere on the middle of the left coast