How to limit search in table partition?

Question

PostgreSQL

there is an events table

• eventtype_id integer NOT NULL
  
• order_id integer
  
• product_id integer
  
• user_id integer
  
• values ​​character varying(64)
  
• dtime timestamp with time zone
  
• id bigint NOT NULL nextval('events_id_seq'::regclass)
  
• config_id integer
  

spoiler

Table "public.events"
Column | Type | Modifiers
--------------+--------------------------+-----------------------------------------------------
eventtype_id | integer | not null
order_id | integer |
product_id | integer |
user_id | integer |
values | character varying(64) |
dtime | timestamp with time zone | not null default now()
id | bigint | not null default nextval('events_id_seq'::regclass)
config_id | integer |
Indexes:
"events_pkey" PRIMARY KEY, btree (id)
"dtime" btree (dtime)
"eventtype_id" btree (eventtype_id)
"order_id" btree (order_id)
"product_id" btree (product_id)
"users_id" btree (user_id)
Triggers:
insert_events_trigger BEFORE INSERT ON events FOR EACH ROW EXECUTE PROCEDURE events_insert_trigger()
Number of child tables: 11 (Use \d+ to list them.)

--
a partition is installed on it, which writes to the public.eventsYYYYmMM table (for example, events2016m02)
if you do an explain select
, it displays that the search is going through all 12 tables, and not by a specific table associated with the current date.
How to make POSTGRESS look in a specific table that refers to the date specified in the request.
EXPLAIN select count (*) as total_visits from events where events.eventtype_id = 8 and dtime >= '2019-09-01'
--

EXPLAIN

Aggregate (cost=152.37..152.38 rows=1 width=0)
-> Nested Loop (cost=18.15..152.35 rows=5 width=0)
-> Seq Scan on users (cost=0.00..1.38 rows=1 width=4)
Filter: (id = 42)
-> Nested Loop (cost=18.15..150.93 rows=5 width=4)
Join Filter: (products.seller_id = sellers.id)
-> Hash Join (cost=18.15..148.65 rows=12 width=4)
Hash Cond: (events.product_id = products.id)
-> Append (cost=0.00..130.33 rows=12 width=4)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Index Scan using events2016m02_dtime on events2016m02 (cost=0.15..8.17 rows=1 width=4)
Index Cond: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone))
Filter: (eventtype_id = 8)
-> Seq Scan on events2016m03 (cost=0.00..21.40 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m04 (cost=0.00..5.61 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m05 (cost=0.00..16.65 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m06 (cost=0.00..2.40 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m07 (cost=0.00..16.65 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m08 (cost=0.00..16.65 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m09 (cost=0.00..16.65 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m10 (cost=0.00..16.65 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Seq Scan on events2016m11 (cost=0.00..1.21 rows=1 width=4)
Filter: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone) AND (eventtype_id = 8))
-> Index Scan using events2016m12_dtime on events2016m12 (cost=0.28..8.30 rows=1 width=4)
Index Cond: ((dtime >= '2017-09-01 00:00:00+03'::timestamp with time zone) AND (dtime <= '2017-09-01 17:06:28+03'::timestamp with time zone))
Filter: (eventtype_id = 8)
-> Hash (cost=17.51..17.51 rows=51 width=8)
-> Seq Scan on products (cost=0.00..17.51 rows=51 width=8)
-> Materialize (cost=0.00..1.22 rows=6 width=8)
-> Seq Scan on sellers (cost=0.00..1.19 rows=6 width=8)
Filter: (user_id = 42)

EXPLAIN select * from events WHERE id = 105;

EXPLAIN

EXPLAIN

Append (cost=0.00..130.26 rows=20 width=132)
-> Seq Scan on events (cost=0.00..0.00 rows=1 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m02 (cost=0.00..6.90 rows=2 width=38)
Filter: (id = 105)
-> Seq Scan on events2016m03 (cost=0.00..17.00 rows=2 width=38)
Filter: (id = 105)
-> Seq Scan on events2016m04 (cost=0.00..4.58 rows=2 width=38)
Filter: (id = 105)
-> Seq Scan on events2016m05 (cost=0.00..14.75 rows=2 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m06 (cost=0.00..2.00 rows=1 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m07 (cost=0.00..14.75 rows=2 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m08 (cost=0.00..14.75 rows=2 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m09 (cost=0.00..14.75 rows=2 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m10 (cost=0.00..14.75 rows=2 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m11 (cost=0.00..1.15 rows=1 width=182)
Filter: (id = 105)
-> Seq Scan on events2016m12 (cost=0.00..24.89 rows=1 width=38)
Filter: (id = 105)

Answer 1

Rechecked the guess

melkij=> create table events2016m11 (check (dtime >= '2016-11-01'::date AND dtime < '2016-12-01'::date)) inherits(events);
melkij=> insert into events2016m11 values ('2016-11-20');
INSERT 0 1
melkij=> explain (analyze) select * from events where dtime > '2016-12-05';
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..38.25 rows=754 width=8) (actual time=0.013..0.013 rows=0 loops=1)
   ->  Seq Scan on events  (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
   ->  Seq Scan on events2016m11  (cost=0.00..38.25 rows=753 width=8) (actual time=0.009..0.009 rows=0 loops=1)
         Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
         Rows Removed by Filter: 1
 Planning time: 0.127 ms
 Execution time: 0.032 ms
(8 строк)
melkij=> drop table events2016m11 ;
DROP TABLE
melkij=> create table events2016m11 (check (dtime >= '2016-11-01'::timestamptz AND dtime < '2016-12-01'::timestamptz)) inherits(events);
CREATE TABLE
melkij=> insert into events2016m11 values ('2016-11-20');INSERT 0 1
melkij=> explain (analyze) select * from events where dtime > '2016-12-05';                                              QUERY PLAN                                              
------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
   ->  Seq Scan on events  (cost=0.00..0.00 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=1)
         Filter: (dtime > '2016-12-05 00:00:00+03'::timestamp with time zone)
 Planning time: 0.301 ms
 Execution time: 0.024 ms
(5 строк)

melkij=> show constraint_exclusion ;
 constraint_exclusion 
----------------------
 partition

Be careful with explicit type conversion. The scheduler has enough work to do and does not consider everything equivalent to be identical. In addition, timestamp with timezone and date (without time at all) must be compared carefully.