Partitioned Tables and ORDER BY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



We have been using partitioning for some time with great success. Up until now our usage has not included ordering and now that we are trying to use an ORDER BY against an indexed column a rather significant shortcoming seems to be kicking in.

Parent table (have cut all but 4 columns to make it easier to post about)
CREATE TABLE people
(
  person_id character varying(36) NOT NULL,
  list_id character varying(36) NOT NULL,
  first_name character varying(255),
  last_name character varying(255),
  CONSTRAINT people_pkey (person_id, list_id)
);

A partition looks like this:
CREATE TABLE people_list1
(
  -- inherited columns omitted
CONSTRAINT people_list1_list_id_check CHECK (list_id::text = 'the_unique_list_id'::text)
)
INHERITS (people);

Both the parent and the children have indexes on all 4 columns mentioned above. The parent table is completely empty.

If I run this query, directly against the partition, performance is excellent:
select * from people_list1 order by first_name asc limit 50;

The explain analyze output:
Limit (cost=0.00..4.97 rows=50 width=34315) (actual time=49.616..522.464 rows=50 loops=1) -> Index Scan using idx_people_first_name_list1 on people_list1 (cost=0.00..849746.98 rows=8544854 width=34315) (actual time=49.614..522.424 rows=50 loops=1)
 Total runtime: 522.773 ms

If I run this query, against the parent, performance is terrible:
select * from people where list_id = 'the_unique_list_id' order by first_name asc limit 50;

The explain analyze output:
Limit (cost=726844.88..726845.01 rows=50 width=37739) (actual time=149864.869..149864.884 rows=50 loops=1) -> Sort (cost=726844.88..748207.02 rows=8544855 width=37739) (actual time=149864.868..149864.876 rows=50 loops=1)
         Sort Key: public.people.first_name
         Sort Method:  top-N heapsort  Memory: 50kB
-> Result (cost=0.00..442990.94 rows=8544855 width=37739) (actual time=0.081..125837.332 rows=8545138 loops=1) -> Append (cost=0.00..442990.94 rows=8544855 width=37739) (actual time=0.079..111103.743 rows=8545138 loops=1) -> Index Scan using people_pkey on people (cost=0.00..4.27 rows=1 width=37739) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: ((list_id)::text = 'the_unique_list_id'::text) -> Seq Scan on people_list1 people (cost=0.00..442986.67 rows=8544854 width=34315) (actual time=0.068..109781.308 rows=8545138 loops=1) Filter: ((list_id)::text = 'the_unique_list_id'::text)
 Total runtime: 149865.411 ms

Just to show that partitions are setup correctly, this query also has excellent performance: select * from people where list_id = 'the_unique_list_id' and first_name = 'JOE';

Here is the explain analyze for that:
Result (cost=0.00..963.76 rows=482 width=37739) (actual time=6.031..25.394 rows=2319 loops=1) -> Append (cost=0.00..963.76 rows=482 width=37739) (actual time=6.029..21.340 rows=2319 loops=1) -> Index Scan using idx_people_first_name on people (cost=0.00..4.27 rows=1 width=37739) (actual time=0.010..0.010 rows=0 loops=1)
               Index Cond: ((first_name)::text = 'JOE'::text)
               Filter: ((list_id)::text = 'the_unique_list_id'::text)
-> Bitmap Heap Scan on people_list1 people (cost=8.47..959.49 rows=481 width=34315) (actual time=6.018..20.968 rows=2319 loops=1)
               Recheck Cond: ((first_name)::text = 'JOE'::text)
               Filter: ((list_id)::text = 'the_unique_list_id'::text)
-> Bitmap Index Scan on idx_people_first_name_list1 (cost=0.00..8.35 rows=481 width=0) (actual time=5.566..5.566 rows=2319 loops=1)
                     Index Cond: ((first_name)::text = 'JOE'::text)
 Total runtime: 25.991 ms


This is Postgres 8.3.7 on the 2.6.28 kernel with constraint_exclusion on. Our partitions are in the 8 - 15 million row range.

I realize one option is to hit the partition directly instead of hitting the parent table with the check constraint in the WHERE clause, but up until now we have been able to avoid needing partition-awareness in our code. Perhaps we have hit upon something that will require breaking that cleanliness but wanted to see if there were any workarounds.

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux