problem with partitioning and indexed order by

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема problem with partitioning and indexed order by
Дата
Msg-id 9e4684ce0601040732s162c7ff2mc9d777c96d263b5@mail.gmail.com
обсуждение исходный текст
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Emi Lu
Дата:
Сообщение: Re: When it is better to use "timestamp without time zone"?
Следующее
От: Jaime Casanova
Дата:
Сообщение: Re: Unique transaction ID