> 11 авг. 2016 г., в 13:46, Andrey Zhidenkov <andrey.zhiden...@gmail.com> > написал(а): > > 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 :) > > What am I doing wrong? > > -- > Andrey Zhidenkov > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- May the force be with you… https://simply.name
