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.
You can make TRUNCATE ONLY master_table. But don’t forget the ONLY keyword because in that case it will truncate all child tables also :)
