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
