Разделение по диапазону дат PostgreSQL сканирует все разделы

У меня есть таблица, разделенная на месяц (столбец временной метки).

при запросе данных объяснение показывает, что все разделы запрашиваются, когда я создаю дату с функциями даты, тогда как когда я использую жестко закодированные даты, сканируются только целевые разделы.

Итак, при таком запросе:

SELECT * FROM vw_comments 
      WHERE created >= '2019-4-1'
        AND created <= '2019-4-30'
limit 100;

он сканирует только 1 раздел (1 месяц, хорошо!), но затем, чтобы сделать его более динамичным, я передаю что-то вроде этого (упрощенное)

SELECT * FROM vw_comments 
      WHERE created >= (date_trunc('month', now()))::timestamp
        AND created <= (date_trunc('month', now() + interval '1 month') - interval '1 day') ::timestamp
limit 100;

те же самые даты получаются из вышеуказанных методов даты, что и первый запрос, но EXPLAIN показывает, что все разделы сканируются.

Как заставить работать?

изменить: добавить определение таблицы и пояснить

по запросу от @a_horse_with_no_name я добавил фактическую таблицу и объяснил. Сделав это, я понял кое-что еще: динамические даты не работают при объединении. Таким образом, если в запросе ниже не указана таблица «пользователи», динамические даты будут работать.

CREATE TABLE public.comments
(
    comment_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    comment_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    from_user_id integer NOT NULL,
    fk_topic_id integer NOT NULL,
    comment_text text COLLATE pg_catalog."default",
    parent_comment_id integer,
    created timestamp without time zone NOT NULL,
    comment_type integer NOT NULL DEFAULT 0,
    CONSTRAINT comments_pkey PRIMARY KEY (comment_id, created)
) PARTITION BY RANGE (created) 
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.comments
    OWNER to soichat;

CREATE INDEX ix_comments_comment_id
    ON public.comments USING btree
    (comment_id DESC)
    TABLESPACE pg_default;

CREATE INDEX ix_comments_created
    ON public.comments USING btree
    (created DESC)
    TABLESPACE pg_default;

CREATE INDEX ix_comments_fk_topic_id
    ON public.comments USING btree
    (fk_topic_id)
    TABLESPACE pg_default;

CREATE INDEX ix_comments_from_user_id
    ON public.comments USING btree
    (from_user_id)
    TABLESPACE pg_default;

CREATE INDEX ix_comments_parent_comment_id
    ON public.comments USING btree
    (parent_comment_id)
    TABLESPACE pg_default;

-- Partitions SQL

CREATE TABLE public.comments_2019_2 PARTITION OF public.ix_comments_parent_comment_id
    FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00');

CREATE TABLE public.comments_2019_3 PARTITION OF public.ix_comments_parent_comment_id
    FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00');

CREATE TABLE public.comments_2019_4 PARTITION OF public.ix_comments_parent_comment_id
    FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00');

CREATE TABLE public.comments_2019_5 PARTITION OF public.ix_comments_parent_comment_id
    FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00');

запрос:

explain (analyse, buffers)
 SELECT comments.comment_id,
    comments.from_user_id,
    comments.fk_topic_id,
    comments.comment_text,
    comments.parent_comment_id,
    comments.created,
    users.user_name,
    users.picture_path
   FROM comments
     LEFT JOIN users ON comments.from_user_id = users.user_id
    WHERE comments.created >= (date_trunc('month', now()))::timestamp
        AND comments.created <= (date_trunc('month', now() + interval '1 month') - interval '1 day') ::timestamp
limit 100;

объяснять (анализировать, буферы)

Limit  (cost=1.20..11.93 rows=100 width=126) (actual time=1.441..1.865 rows=100 loops=1)
  Buffers: shared hit=499
  ->  Merge Left Join  (cost=1.20..753901.07 rows=7028011 width=126) (actual time=1.440..1.778 rows=100 loops=1)
        Merge Cond: (comments_2019_2.from_user_id = users.user_id)
        Buffers: shared hit=499
        ->  Merge Append  (cost=0.92..665812.08 rows=7028011 width=51) (actual time=0.017..0.259 rows=100 loops=1)
              Sort Key: comments_2019_2.from_user_id
              Buffers: shared hit=15
              ->  Index Scan using comments_2019_2_from_user_id_idx on comments_2019_2  (cost=0.15..58.95 rows=5 width=56) (actual time=0.002..0.003 rows=0 loops=1)
                    Filter: ((created >= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created <= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                    Buffers: shared hit=1
              ->  Index Scan using comments_2019_3_from_user_id_idx on comments_2019_3  (cost=0.15..9790.24 rows=1 width=51) (actual time=0.002..0.003 rows=0 loops=1)
                    Filter: ((created >= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created <= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                    Buffers: shared hit=1
              ->  Index Scan using comments_2019_4_from_user_id_idx on comments_2019_4  (cost=0.43..550483.74 rows=7028000 width=51) (actual time=0.010..0.162 rows=100 loops=1)
                    Filter: ((created >= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created <= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                    Buffers: shared hit=12
              ->  Index Scan using comments_2019_5_from_user_id_idx on comments_2019_5  (cost=0.15..58.95 rows=5 width=56) (actual time=0.001..0.002 rows=0 loops=1)
                    Filter: ((created >= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created <= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                    Buffers: shared hit=1
        ->  Index Scan using pk_users on users  (cost=0.28..234.83 rows=1606 width=79) (actual time=0.005..0.870 rows=1395 loops=1)
              Buffers: shared hit=484
Planning Time: 0.360 ms
Execution Time: 1.942 ms

postgresql database-partitioning postgresql-11
person Elger Mensonides    schedule 11.04.2019    source источник
comment
@a_horse_with_no_name Я добавил собственно запрос и объяснил. Сделав это, я понял кое-что еще: динамические даты не работают при объединении. Таким образом, если в запросе ниже не указана таблица «пользователи», динамические даты будут работать.   -  person Elger Mensonides    schedule 11.04.2019
comment
@a_horse_with_no_name: Хм, я использовал dbeaver для создания скрипта. Я обновил вопрос, указав правильный сценарий создания из pgadmin. Прости за это.   -  person Elger Mensonides    schedule 11.04.2019
comment
Похоже, этот случай слишком сложен для PostgreSQL, чтобы выполнять обрезку разделов; Я не знаю точной причины. Но сканирование индексов на других разделах на самом деле не повредит, не так ли?   -  person Laurenz Albe    schedule 11.04.2019
comment
@LaurenzAlbe кажется так, но вы видите результаты тестовой базы данных (почти без записей в другие месяцы), обычно есть миллионы комментариев в месяц, мы удаляем их через 1 год, так что тогда это будет вроде больно. В настоящее время секционирование в продакшене еще не реализовано.   -  person Elger Mensonides    schedule 11.04.2019

Ответы (1)


arrow_upward
5
arrow_downward

Нашел (отличный) ответ здесь

Поскольку планировщик не может знать, какое время будет выдавать time now () во время выполнения, он выбирает безопасный вариант и просматривает все разделы. Поскольку я не хочу настраивать новые функции для каждого раздела, я выбрал неизменяемую функцию, которая создает дату:

CREATE FUNCTION now_immutable()
  RETURNS timestamp AS
$func$
SELECT now() AT TIME ZONE current_setting('TimeZone')
$func$  LANGUAGE sql IMMUTABLE;

так что теперь вместо использования now () я просто использую эту функцию для функций, в которых дата не меняется в транзакции:

explain (analyse, buffers)
 SELECT comments.comment_id,
    comments.from_user_id,
    comments.fk_topic_id,
    comments.comment_text,
    comments.parent_comment_id,
    comments.created,
    users.user_name,
    users.picture_path
   FROM comments
     LEFT JOIN users ON comments.from_user_id = users.user_id
    WHERE comments.created >= (date_trunc('month', now_immutable()))
        AND comments.created <= (date_trunc('month', now_immutable() + interval '1 month') - interval '1 day') 
limit 100;

Я также создал еще одну удобную функцию для вызова из кода months_back:

    CREATE OR REPLACE FUNCTION public.months_back(months_back integer)
     RETURNS timestamp without time zone
     LANGUAGE sql
     IMMUTABLE
    AS $function$
        SELECT cast((date_trunc('month', now()) - (months_back || ' month')::interval)::timestamp AT TIME ZONE current_setting('TimeZone') as timestamp)  
    $function$;

Это удобно при ежемесячном разбиении, потому что вы можете просто вызвать month_back (3), если вы знаете, что первый комментарий был 3 месяца назад, и postgres будет искать только 3 раздела, передача 0 даст вам начало текущего месяца.

Надеюсь, это кому-то поможет.

person Elger Mensonides    schedule 12.04.2019