I have a table (registry.entry) which has ~ 100 inherited tables. This
is a master table and it's empty:
postgres@db=# select count(*) from only registry.entry;
count
-------
0
(1 row)
Master table has rules, inherited tables has check constraints. Data
partitioned by value of area_id. But when I run a query with area_id
in where clause, planner do seq scan on master table if master table
has no indexes or index scan if has:
Append (cost=0.12..1750.11 rows=670 width=256)
-> Index Scan using MASTER_TABLE_INDEX on entry e (cost=0.12..6.15
rows=1 width=253)
Index Cond: (((cadastral_number)::text ~>=~
'61:44:0030502'::text) AND ((cadastral_number)::text ~<~
'61:44:0030503'::text))
Filter: (((cadastral_number)::text ~~ '61:44:0030502%'::text)
AND (area_id = 1381) AND (quarter_id = 1368779))
-> Bitmap Heap Scan on entry_61_44 e_1 (cost=1381.62..1743.95
rows=669 width=256)
Recheck Cond: (quarter_id = 1368779)
Filter: (((cadastral_number)::text ~~ '61:44:0030502%'::text)
AND (area_id = 1381))
-> BitmapAnd (cost=1381.62..1381.62 rows=122 width=0)
-> Bitmap Index Scan on
entry_61_44_cadastral_number_idx (cost=0.00..321.57 rows=12901
width=0)
Index Cond: (((cadastral_number)::text ~>=~
'61:44:0030502'::text) AND ((cadastral_number)::text ~<~
'61:44:0030503'::text))
-> Bitmap Index Scan on entry_61_44_quarter_id_idx
(cost=0.00..1059.47 rows=67205 width=0)
Index Cond: (quarter_id = 1368779)
As you can see, postgres scan only one needed partition and (!) an
index from master table, In this example I has an index on master
table because it's a production server and when I drop it query time
is too long.
In the past (before partitioning) master table has many rows. I made
vacuum and vacuum analyze for registry.entry, but it didn't help.
pgAdmin says that table size is 21Gb, live tuples: 0, dead tuples: 0.
