problem with partitioning and indexed order by

From: hubert depesz lubaczewski <depesz(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: problem with partitioning and indexed order by
Date: 2006-01-04 15:32:05
Message-ID: 9e4684ce0601040732s162c7ff2mc9d777c96d263b5@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

hi
i have a table with 14 milion columns.
structure is like this:
id (serial), object_id (int8), first_id (int8), second_id (int8), counter
(int8)

id is primary key,
(object_id, first_id, second_id) form together unique index.
i partitioned it using first_id and second_id as check params.
works.
but!
when i query specific partition directly i get:
test=# explain
test-# select acr.object_id, acr.counter
test-# from acr.acr_counter_c1_r1 acr
test-# where
test-# acr.first_id=1
test-# and acr.r_id=1
test-# order by
test-# acr.first_id desc, acr.r_id desc, acr.counter desc
test-# limit 30 offset 0
test-# ;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.28 rows=30 width=32)
-> Index Scan Backward using acr_counter_c1_r1_idx on acr_counter_c1_r1
acr (cost=0.00..55161.38 rows=1295688 width=32)
Index Cond: ((first_id = 1) AND (r_id = 1))
(3 rows)

which is perfect.
*but*. when i query master table instead, i get:
test=# explain
test-# select acr.object_id, acr.counter
test-# from acr.acr_counter acr
test-# where
test-# acr.first_id=1
test-# and acr.r_id=1
test-# order by
test-# acr.first_id desc, acr.r_id desc, acr.counter desc
test-# limit 30 offset 0
test-# ;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------
Limit (cost=183338.81..183338.88 rows=30 width=32)
-> Sort (cost=183338.81..186578.03 rows=1295688 width=32)
Sort Key: acr.first_id, acr.r_id, acr.counter
-> Result (cost=0.00..31545.32 rows=1295688 width=32)
-> Append (cost=0.00..31545.32 rows=1295688 width=32)
-> Seq Scan on acr_counter_c1_r1 acr (cost=
0.00..31545.32 rows=1295688 width=32)
Filter: ((first_id = 1) AND (r_id = 1))
(7 rows)

which is definitelly bad!
basically it disables indexing?!

why is that so, what cen be done by me to improve it (i would rather not
modify my system to query specific partitions), and perhaps what and when
cen be done by postgresql hackers to improve it?

best regards

depesz

Browse pgsql-general by date

  From Date Subject
Next Message Jaime Casanova 2006-01-04 15:36:19 Re: Unique transaction ID
Previous Message Emi Lu 2006-01-04 15:07:54 Re: When it is better to use "timestamp without time zone"?