PostgreSQL. Как ускорить поиск по подзапросу с оконной функцией если он сканирует все данные большой таблицы?

Question

Есть довольно большая таблица h_obj (больше 146.000.000 записей), которая хранит логи. И есть вот такой запрос, который должен определить порядкое число записи в этой таблице по id. Данный запрос выполняется непозволительно долго - 6 минут.. Помогите, пожалуйста, ускорить его.

Скрипт создания таблицы h_obj и индексов для нее.

CREATE TABLE IF NOT EXISTS public.h_obj
(
    h_obj_id integer NOT NULL DEFAULT nextval('seq_h_obj_h_obj_id'::regclass),
    obj_type_id smallint NOT NULL,
    obj_id integer NOT NULL,
    log_type_id smallint NOT NULL,
    old_params character varying(3000) COLLATE pg_catalog."default",
    new_params character varying(3000) COLLATE pg_catalog."default",
    modified_by character varying(30) COLLATE pg_catalog."default" NOT NULL,
    modified_by_name character varying(250) COLLATE pg_catalog."default" NOT NULL,
    modified_when timestamp without time zone NOT NULL DEFAULT (now() AT TIME ZONE 'utc'::text),
    obj_id_smallint smallint GENERATED ALWAYS AS (
CASE
    WHEN ((obj_id >= '-32768'::integer) AND (obj_id <= 32767)) THEN obj_id
    ELSE NULL::integer
END) STORED,
    CONSTRAINT pk_h_obj PRIMARY KEY (h_obj_id)
)

CREATE INDEX IF NOT EXISTS ix_h_log_type_id
    ON public.h_obj USING btree
    (log_type_id ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS ix_h_obj_status_log
    ON public.h_obj USING btree
    (obj_id ASC NULLS LAST, obj_type_id ASC NULLS LAST)
    INCLUDE(old_params)
    TABLESPACE pg_default
    WHERE log_type_id = ANY (ARRAY[6, 170]);

CREATE INDEX IF NOT EXISTS ix_h_obj_type_id
    ON public.h_obj USING btree
    (obj_id ASC NULLS LAST, obj_type_id ASC NULLS LAST)
    TABLESPACE pg_default;

Из того, что было опробовано для повышения перформанса: Добавлен индекс по log_type_id для h_obj, не помогло. Перенос условия с Id = 2 в подзапрос с INNER JOIN внутри, ищет быстро - но вроде как не правильно подсчитывает ROW_NUMBER. Были мысли о полусвязывание таблиц и секционирование, но решил сначала обсудить с вами. Спасибо

SELECT 
  CAST(RowNumber AS int) AS RowNumber, 
  Id 
FROM 
  (
    SELECT 
      ROW_NUMBER() OVER (
        ORDER BY 
          ROW_NUMBER_QUERY."h_obj_id" ASC
      ) AS RowNumber, 
      CAST(ROW_NUMBER_QUERY.H_OBJ_ID AS int) AS Id 
    FROM 
      (
        SELECT 
          "Extent1"."h_obj_id", 
          "Extent1"."obj_type_id", 
          "Extent1"."obj_id", 
          "Extent1"."log_type_id", 
          "Extent1"."old_params", 
          "Extent1"."new_params", 
          "Extent1"."modified_by", 
          "Extent1"."modified_by_name", 
          "Extent1"."modified_when", 
          "Extent1"."obj_id_smallint", 
          "Extent2"."log_type_id" AS "log_type_id1", 
          "Extent2"."log_type_name" 
        FROM 
          "public"."h_obj" AS "Extent1" 
          INNER JOIN "public"."d_s_log_type" AS "Extent2" ON "Extent1"."log_type_id" = "Extent2"."log_type_id"
      ) AS ROW_NUMBER_QUERY
  ) AS ROW_NUMBER_SUB_QUERY 
WHERE 
  ROW_NUMBER_SUB_QUERY.Id = 2

Answer 1

Начну с проблем в коде, которые заметил

Избыточное использование подзапросов и ROW_NUMBER(). Текущий запрос трижды выполняет вложенные операции и сортировки. Это дорого на 146+ млн записей.

INNER JOIN с d_s_log_type не даёт выигрыша Таблица d_s_log_type (если это справочник) не нужна, если мы не используем её колонки в ORDER BY.

ORDER BY h_obj_id ASC без правильного индекса вызывает Seq Scan.

Фильтрация WHERE Id = 2 происходит после всех вычислений Нужно фильтровать раньше, а не после нумерации строк.

Оптимизация

Если вы часто ищете порядковый номер по h_obj_id, нужен индекс по h_obj_id:

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_h_obj_id ON public.h_obj (h_obj_id);

Если log_type_id всё же используется, можно сделать состовной индекс:

CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_h_obj_id_log_type ON public.h_obj (h_obj_id, log_type_id

Можно использовать подзапрос с COUNT(*) вместо ROW_NUMBER()

SELECT COUNT(*) + 1 AS RowNumber, h_obj_id AS Id
FROM h_obj
WHERE h_obj_id < (SELECT h_obj_id FROM h_obj WHERE h_obj_id = 2);

Это значительно быстрее – вместо сортировки считаем число записей перед нужной строкой.

Использовать LATERAL JOIN

SELECT RowNumber, h_obj_id AS Id
FROM (
    SELECT h_obj_id, ROW_NUMBER() OVER (ORDER BY h_obj_id) AS RowNumber
    FROM h_obj
) AS sub
WHERE sub.h_obj_id = 2;

Этот вариант работает быстрее вашего текущего запроса, но медленнее, чем COUNT(*).

Answer 2

Посмотрите пример. Id=2000.

EXPLAIN ANALYZE
SELECT CAST(RowNumber AS int) AS RowNumber, Id 
FROM 
  (
    SELECT 
 --     ROW_NUMBER() OVER (ORDER BY ROW_NUMBER_QUERY."h_obj_id" ASC ) AS RowNumber, 
      count(*)over() as RowNumber,
      ROW_NUMBER_QUERY.H_OBJ_ID AS Id 
    FROM  "public"."h_obj" AS ROW_NUMBER_QUERY
    where ROW_NUMBER_QUERY."h_obj_id"<=2000
  ) AS ROW_NUMBER_SUB_QUERY 
WHERE ROW_NUMBER_SUB_QUERY.Id = 2000

Пример плана на таблице в 40К строк.

QUERY PLAN
Subquery Scan on row_number_sub_query  (cost=625.56..709.05 rows=33 width=8) (actual time=1.086..1.088 rows=1 loops=1)
  Filter: (row_number_sub_query.id = 2000)
  Rows Removed by Filter: 1999
  ->  WindowAgg  (cost=625.56..625.63 rows=6667 width=12) (actual time=0.712..0.976 rows=2000 loops=1)
        ->  Bitmap Heap Scan on h_obj row_number_query  (cost=131.96..542.29 rows=6667 width=4) (actual time=0.112..0.383 rows=2000 loops=1)
              Recheck Cond: (h_obj_id <= 2000)
              Heap Blocks: exact=32
              ->  Bitmap Index Scan on ix_h_obj_h_obj_id  (cost=0.00..130.29 rows=6667 width=0) (actual time=0.093..0.094 rows=2000 loops=1)
                    Index Cond: (h_obj_id <= 2000)
Planning Time: 1.409 ms
Execution Time: 1.146 ms

Fiddle

П.С. Добавил индекс ix_h_obj_h_obj_id (для надежности))

БЛОГ НА HUSL

PostgreSQL. Как ускорить поиск по подзапросу с оконной функцией если он сканирует все данные большой таблицы?

Ответы (2 шт):