Оптимизация запроса в базу данных Postgresql с 10 миллионами записями

Question

Есть база даных я и делаю 2 запроса при перезагрузки страницы и этот запрос выполняеться около 7 секунд, подскажите как я могу оптимизировать его?

SELECT DISTINCT vehicle_year FROM auction_result_values;
SELECT DISTINCT make FROM auction_result_values;

структура таблицы, сразу уточню я ставил индексы на данные столбцы которые я ищу, тип у них BTREE, но это особо не помогло, как было 7 секунд так и осталось

CREATE TABLE auction_result_values (
id bigserial PRIMARY KEY,
auction_ident varchar(32),
id_result bigint,
cdate date,
ctime time without time zone,
week smallint,
year integer,
month smallint,
day_of_week smallint,
make varchar(32),
model varchar(64),
vehicle_year integer,
trim varchar(64),
color varchar(32),
doors varchar(1),
cylinders varchar(2),
fuel varchar(12),
transmission varchar(1),
radio varchar(12),
top varchar(12),
int varchar(12),
drive_wheels varchar(12),
ew varchar(12),
odometer bigint,
price numeric(10,2),
is_excluded boolean,
is_good_deal boolean
);

CREATE INDEX idx_13835784_auction_ident ON auction_result_values USING btree (auction_ident);

CREATE INDEX idx_13835784_id_result ON auction_result_values USING btree (id_result);

ALTER TABLE ONLY auction_result_values
ADD CONSTRAINT auction_result_values_ibfk_1 FOREIGN KEY (auction_ident) REFERENCES auctions(ident) ON UPDATE CASCADE ON DELETE RESTRICT;

ALTER TABLE ONLY auction_result_values
ADD CONSTRAINT auction_result_values_ibfk_2 FOREIGN KEY (id_result) REFERENCES auction_results(id) ON UPDATE CASCADE ON DELETE CASCADE;

Answer 1

На моём опыте SELECT DISTINCT без каких-либо условий и группировок является признаком недонормализованной схемы. Возьмём, к примеру, поле make. Я так понимаю, это производитель автомобиля? Это же первый кандидат на вынос из таблицы! Так что первая рекомендация — сделайте нормальную схему!

Опять же из опыта могу сказать, что простое добавление индекса может помочь, а может и нет. Есть как минимум две техники оптимизации, которые могут (не) сработать. Первая — использование GROUP BY вместо DISTINCT:

SELECT make FROM t_1 GROUP BY make;

Иногда это работает быстрее. Вторая техника — так называемое разреженное сканирование индекса (англ. «loose indexscan»), который в PostgreSQL отсутствует, но эмулируется с помощью рекурсивных общих табличных выражений (англ. «recursive common table expressions»):

WITH RECURSIVE t_2 AS (
  (SELECT make FROM t_1 ORDER BY make LIMIT 1)
  UNION ALL
  (
    SELECT
    (
      SELECT CAST(MIN(make) AS CHARACTER VARYING(32))
        FROM t_1
       WHERE make > t_2.make
    )
      FROM t_2
     WHERE t_2.make IS NOT NULL
  )
)
SELECT make
  FROM t_2
 WHERE make IS NOT NULL
;

Очевидно, тут уже нужен индекс. Работает медленнее на небольших данных, но может дать значительный прирост на больших (до × 20, на моём опыте). Плюс всегда есть материализованные представления (англ. «materialised views»), но тут уже сами, там нетрудно.

Answer 2

Не делать такие запросы.

На момент релиза 13 версии postgresql не умеет автоматически выполнять loose indexscan. Собственно индекс и вовсе не используется. А потому поиск малого числа уникальных записей среди многих миллионов неэффективен даже при наличии индекса.

Можно научить делать loose indexscan вручную: вместо запроса

select
  distinct author_id
from b_p_t

Написать

WITH RECURSIVE t AS (
(SELECT author_id AS _author_id FROM b_p_t ORDER BY author_id LIMIT 1)
UNION ALL
SELECT author_id AS _author_id FROM t, LATERAL (
  --find the next author_id > current author_id
  SELECT author_id FROM b_p_t WHERE author_id>t._author_id
  ORDER BY author_id LIMIT 1
) AS a_id
)
SELECT _author_id FROM t;

Что выглядит устрашающе, ещё более впечатляюще в explain, но работает на порядки быстрее distinct при наличии индекса. (пример запроса с доклада)

Или вовсе пересмотреть задачу. Для чего эти данные нужны, может быть их кэшировать и обновлять кэш в фоне. Либо вовсе хранить предаггрегированное состояние в materialized view либо крохотной, обновляемой триггерами, табличке.

БЛОГ НА HUSL

Оптимизация запроса в базу данных Postgresql с 10 миллионами записями

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