pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPlanner do seq scan on empty master partitioned table
3+ messages / 3 participants
[nested] [flat]
* Planner do seq scan on empty master partitioned table
@ 2016-08-11 10:46 Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
0 siblings, 2 replies; 3+ messages in thread
From: Andrey Zhidenkov @ 2016-08-11 10:46 UTC (permalink / raw)
To: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Planner do seq scan on empty master partitioned table
@ 2016-08-11 13:50 Tom Lane <tgl@sss.pgh.pa.us>
parent: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2016-08-11 13:50 UTC (permalink / raw)
To: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>; +Cc: pgsql-performance
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Planner do seq scan on empty master partitioned table
@ 2016-08-11 13:55 Vladimir Borodin <root@simply.name>
parent: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Vladimir Borodin @ 2016-08-11 13:55 UTC (permalink / raw)
To: Andrey Zhidenkov <andrey.zhidenkov@gmail.com>; +Cc: pgsql-performance
> 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
--
May the force be with you…
https://simply.name
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2016-08-11 13:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-08-11 10:46 Planner do seq scan on empty master partitioned table Andrey Zhidenkov <andrey.zhidenkov@gmail.com>
2016-08-11 13:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-08-11 13:55 ` Vladimir Borodin <root@simply.name>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox