Задать вопрос
@masimka
postgresql

Как ограничить поиск в партиции таблицы?

есть таблица
events
  • 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.)

--
на ней установлена партиция, которая пишет в таблицу public.eventsYYYYmMM (например events2016m02)
если сделать explain select
то оно выводит что поиск идёт перебором по всем 12 таблицам, а не по конкретной таблице связаной с текущей датой.
Как сделать так что бы POSTGRESS искал в конкретной таблице которая относится к дате указанной в запросе.

Вот что выводит 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)

  • Вопрос задан
  • 222 просмотра
2 комментария
Подписаться 1 Средний 2 комментария
Пригласить эксперта
Ответы на вопрос 1
Melkij
@Melkij
PostgreSQL DBA
Перепроверил догадку
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

Внимательнее с явным приведением типов. У планировщика достаточно работы и не всё эквивалентное он считает идентичным. К тому же timestamp with timezone и date (без времени вовсе) надо сравнивать аккуратно.
Ответ написан
Комментировать
Комментировать
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы
Вакансии с Хабр Карьеры
Разработчик PostgreSQL
Neoflex • Москва
от 90 000 до 250 000 ₽
DBA / Администратор баз данных PostgreSQL
СберТех • Москва
от 320 000 ₽
Администратор PostgreSQL
Гринатом
До 200 000 ₽
Ещё вакансии
Заказы с Хабр Фриланса
Конвертировать файл в формате mkv в mp4
18 дек. 2024, в 20:01
1 руб./за проект
Веб сервис для печатной мастерской (php+laravel+mysql)
18 дек. 2024, в 19:48
100000 руб./за проект
Отладить код на JavaScript
18 дек. 2024, в 18:35
500 руб./в час
Ещё заказы