Обсуждение: Planner do seq scan on empty master partitioned table
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. What am I doing wrong? -- Andrey Zhidenkov
Andrey Zhidenkov <andrey.zhidenkov@gmail.com> writes: > I have a table (registry.entry) which has ~ 100 inherited tables. This > is a master table and it's empty: As long as it's empty, a seqscan should be essentially free. Don't worry about it. And definitely don't create indexes, that will just add cost. regards, tom lane
11 авг. 2016 г., в 13:46, Andrey Zhidenkov <andrey.zhidenkov@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