Самый быстрый способ PostgreSQL Различия и Формат

У меня в таблице 3,5 миллиона строк acs_objects и мне нужно получить столбец creation_date с годами только формат и отчетливый.

Моя первая попытка: 180~200 Sec (15 Rows Fetched)

SELECT DISTINCT to_char(creation_date,'YYYY') FROM acs_objects

Моя вторая попытка: 35~40 Sec (15 Rows Fetched)

SELECT DISTINCT to_char(creation_date,'YYYY')
FROM (SELECT DISTINCT creation_date FROM acs_objects) AS distinct_date

Есть ли способ сделать это быстрее? - "Мне нужно использовать это на веб-сайте ADP"

Источник

4 ответа

Решение

Во второй попытке вы получаете разные даты из подзапроса, которые затем все конвертируете в строковое представление, а затем выбираете разные. Это довольно неэффективно. Лучше сначала извлечь отдельные годы из creation_date в подзапросе и просто приведите их к тексту в основном запросе:

SELECT year::text
FROM (
  SELECT DISTINCT extract(year FROM creation_date) AS year FROM acs_objects
) AS distinct_years;

Если вы создаете INDEX на столе запрос должен выполняться намного быстрее:

CREATE INDEX really_fast ON acs_objects((extract(year FROM creation_date)));

Однако это может повлиять на другие виды использования вашей таблицы, в частности, если у вас много изменяющих операторов (вставка, обновление, удаление). И это будет работать только если creation_date имеет тип данных date или же timestamp (конкретно нет timestamp with timezone).

Опция ниже выглядела многообещающе, потому что она не использует подзапрос, но на самом деле она намного медленнее (см. Комментарии ниже), возможно потому, что DISTINCT предложение применяется к строке:

SELECT DISTINCT extract(year FROM creation_date)::text
FROM acs_objects;
Источник

Я думаю, что вы не должны выбирать distinct с этого огромного стола. Вместо этого попробуйте сгенерировать последовательность коротких лет (скажем, с 1900 по 2100) и выбрать из этой последовательности только те годы, которые существуют в acs_objects Таблица. Набор результатов будет таким же, но я думаю, что он будет быстрее. Подзапрос EXISTS должен быстро выполняться на индексированном поле creation_date,

SELECT y 
FROM
(
   select generate_series(1900,2100) as y
) as t
WHERE EXISTS (SELECT 1 FROM acs_objects 
                    WHERE creation_date >= DATE (''||t.y||'-01-01')     
                           AND  creation_date < DATE (''||t.y + 1||'-01-01'))

SQLFiddle demo

Источник

Предположения

  • Текущий Postgres 9,4
  • creation_date это тип данных timestamp (работает на date или же timestamptz так же).
  • Возможные временные метки между 1990 и 2020 годами (но rCTE не нуждается в допущениях).

Основная оценка

Если вам нужно это часто и быстро, то вам подойдет материализованное представление, как предложил @Rogier. Но вам все еще нужен запрос для реализации MV. А приведенные ниже запросы настолько быстры, что вы можете пропустить MV ...

В смежных случаях часто есть справочная таблица со значениями-кандидатами, позволяющая выполнять намного более быстрый запрос:

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

Индекс

Все, что вам нужно, это базовый индекс creation_date, нет специализированного индекса выражения - для любого из трех вариантов, обсуждаемых здесь:

CREATE INDEX foo ON acs_objects (creation_date);

Эмулируйте сканирование свободного индекса с помощью rCTE

Если у вас нет ни справочной таблицы, ни производной таблицы с потенциальными значениями, все равно есть очень быстрая альтернатива. По сути, вам нужно эмулировать "свободное сканирование индекса". Этот запрос работает в любом случае:

WITH RECURSIVE cte AS (
   (
   SELECT creation_date AS y
   FROM   acs_objects
   ORDER  BY creation_date
   LIMIT  1
   )
   UNION ALL
   SELECT u.creation_date
   FROM   cte c
   ,      LATERAL (
      SELECT creation_date
      FROM   acs_objects
      WHERE  creation_date >= date_trunc('year', c.y + interval '1 year')
      ORDER  BY creation_date
      LIMIT  1
      ) u
   )
SELECT to_char(y, 'YYYY') AS year
FROM   cte;

Детальное объяснение:

Postgres Wiki.

На основе generate_series()

Для полноты, идея Valex может быть реализована более эффективно с альтернативной формой generate_series() производства timestamp значения и с некоторыми изменениями:

SELECT to_char(y, 'YYYY') AS year
FROM   generate_series(timestamp '1900-1-1 0:0'
                     , timestamp '2020-1-1 0:0'
                     , interval  '1 year') t(y)
WHERE  EXISTS (
   SELECT 1 FROM acs_objects 
   WHERE creation_date >= y
   AND   creation_date <  y + interval '1 year'
   );

SQL Fiddle демонстрирует оба.

Или вы извлекаете min(creation_date) а также max(creation_date) если вы не можете надежно угадать возможный диапазон лет:

эталонный тест

Я провел быстрый тест на базовой временной таблице с 100 тыс. Строк и указанным индексом в pg 9.4. Лучший из 5 с EXPLAIN (ANALYZE, TIMING OFF):

Общее время выполнения запроса:

valex generate_series: 3.193 ms
erwin generate_series: 1.360 ms
erwin rCTE:            1.044 ms

Вы должны увидеть похожие результаты.

Источник

Я не уверен, для чего ты это используешь. Я, вероятно, рассмотрю возможность использования материализованного представления.

Теперь вы можете обновлять представление при необходимости и иметь очень быстрый способ получения (отдельного) списка годов (поскольку данные в основном хранятся в статическом виде).

Посмотрите здесь:

Источник
Другие вопросы по тегам