Re: Planner do seq scan on empty master partitioned table

> 11 авг. 2016 г., в 13:46, Andrey Zhidenkov <andrey(dot)zhidenkov(at)gmail(dot)com> написал(а):
>
> I have a table (registry.entry) which has ~ 100 inherited tables. This
> is a master table and it's empty:
>
> postgres(at)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance

--
May the force be with you…
https://simply.name