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?
