CREATE TABLE IF NOT EXISTS tasks
(
id bigint not null,
created_date timestamp not null,
status_code integer,
target_identity varchar(255),
updated_date timestamp,
UNIQUE (created_date, target_identity)
) PARTITION BY RANGE (created_date);
CREATE TABLE IF NOT EXISTS tasks2020_04 PARTITION OF tasks FOR VALUES FROM ('2020-04-01') TO ('2020-05-01');
CREATE INDEX IF NOT EXISTS idx_task_created_date ON tasks2020_04 (created_date);
CREATE TABLE IF NOT EXISTS tasks2020_05 PARTITION OF tasks FOR VALUES FROM ('2020-05-01') TO ('2020-06-01');
CREATE INDEX IF NOT EXISTS idx_task_created_date ON tasks2020_05 (created_date);
CREATE TABLE IF NOT EXISTS tasks2020_06 PARTITION OF tasks FOR VALUES FROM ('2020-06-01') TO ('2020-07-01');
CREATE INDEX IF NOT EXISTS idx_task_created_date ON tasks2020_06 (created_date);
CREATE TABLE IF NOT EXISTS tasks2020_07 PARTITION OF tasks FOR VALUES FROM ('2020-07-01') TO ('2020-08-01');
CREATE INDEX IF NOT EXISTS idx_task_created_date ON tasks2020_07 (created_date);
Partition is created, no errors.It's good.
But the problem is, when I execute this query
SET enable_partition_pruning = on;
EXPLAIN ANALYZE select * from tasks p
where p.created_date >= DATE '2020-04-01' AND p.target_identity in ('identity')
It search through each partition. explain analyze:
"QUERY PLAN"
"Append (cost=0.14..206.16 rows=24 width=560) (actual time=0.072..0.072 rows=0 loops=1)"
" -> Index Scan using tasks2020_04_created_date_target_identity_key on tasks2020_04 p (cost=0.14..8.58 rows=1 width=560) (actual time=0.009..0.009 rows=0 loops=1)"
" Index Cond: ((created_date >= '2020-04-01'::date) AND ((target_identity)::text = 'identity'::text))"
" -> Index Scan using tasks2020_05_created_date_target_identity_key on tasks2020_05 p_1 (cost=0.14..8.58 rows=1 width=560) (actual time=0.002..0.003 rows=0 loops=1)"
" Index Cond: ((created_date >= '2020-04-01'::date) AND ((target_identity)::text = 'identity'::text))"
" -> Index Scan using tasks2020_06_created_date_target_identity_key on tasks2020_06 p_2 (cost=0.14..8.58 rows=1 width=560) (actual time=0.002..0.002 rows=0 loops=1)"
" Index Cond: ((created_date >= '2020-04-01'::date) AND ((target_identity)::text = 'identity'::text))"
" -> Index Scan using tasks2020_07_created_date_target_identity_key on tasks2020_07 p_3 (cost=0.14..8.58 rows=1 width=560) (actual time=0.002..0.002 rows=0 loops=1)"
why? maybe some index missing?
on this site, https://postgrespro.ru/docs/postgresql/12/ddl-partitioning#DDL-PARTITION-PRUNING , I do exactly the same
this is from site: SET enable_partition_pruning = on;
EXPLAIN SELECT count(*) FROM measurement WHERE log_date >= DATE '2008-01-01';
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=37.75..37.76 rows=1 width=8)
-> Seq Scan on measurement_y2008m01 (cost=0.00..33.12 rows=617 width=0)
Filter: (log_date >= '2008-01-01'::date)
version:PostgreSQL 12.0 (Debian 12.0-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
The mentionned example is not the same because the WHERE clause allows to scan only one partition: the last one. Here it's the opposite: partitions scan is expected because all scanned partitions match the clause p.created_date >= DATE '2020-04-01. To avoid this you need to give a WHERE clause that limit the list of partitions, for example:
EXPLAIN ANALYZE select * from tasks p
where
p.created_date >= DATE '2020-04-01'
AND p.created_date <= DATE '2020-04-30'
AND p.target_identity in ('identity');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using idx_task_created_date on tasks2020_04 p (cost=0.14..8.17 rows=1 width=544) (actual time=0.004..0.004 rows=0 loops=1)
Index Cond: ((created_date >= '2020-04-01'::date) AND (created_date <= '2020-04-30'::date))
Filter: ((target_identity)::text = 'identity'::text)
Planning Time: 0.182 ms
Execution Time: 0.018 ms
(5 rows)
Or (this query matches more the example given: to scan only the last existing partition):
EXPLAIN ANALYZE select * from tasks p
where
p.created_date >= DATE '2020-07-01'
AND p.target_identity in ('identity');
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tasks2020_07_created_date_target_identity_key on tasks2020_07 p (cost=0.14..8.62 rows=1 width=544) (actual time=0.004..0.005 rows=0 loops=1)
Index Cond: ((created_date >= '2020-07-01'::date) AND ((target_identity)::text = 'identity'::text))
Planning Time: 0.074 ms
Execution Time: 0.018 ms
(4 rows)