Оптимизировать оператор даты BETWEEN

Мне нужна помощь в оптимизации запроса PostgreSQL, который использует предложение BETWEEN с полем timestamp.

У меня есть 2 таблицы:

ONE(int id_one(PK), datetime cut_time, int f1 . . .)

содержащий около 3394 строк

TWO(int id_two(PK), int id_one(FK), int f2 . . .)

содержащий около 4000000 строк

Индексы btree есть на ПК id_one и id_two, на FK id_one и cut_time.

Я хочу выполнить запрос типа:

select o.id_one, Date(o.cut_time), o.f1, t.f2 
from one o
inner join two t ON (o.id_one = t.id_one)
where o.cut_time between '2013-01-01' and '2013-01-31';

Этот запрос извлекает около 1 700 000 строк примерно за 7 секунд.

Ниже приводится отчет об анализе объяснения:

"Merge Join  (cost=20000000003.53..20000197562.38 rows=1680916 width=24) (actual time=0.017..741.718 rows=1692345 loops=1)"
"  Merge Cond: (c.coilid = hf.coilid)"
"  ->  Index Scan using pk_coils on coils c  (cost=10000000000.00..10000000382.13 rows=1420 width=16) (actual time=0.008..4.539 rows=1404 loops=1)"
"        Filter: ((cut_time >= '2013-01-01 00:00:00'::timestamp without time zone) AND (cut_time <= '2013-01-31 00:00:00'::timestamp without time zone))"
"        Rows Removed by Filter: 1990"
"  ->  Index Scan using idx_fk_lf_data on hf_data hf  (cost=10000000000.00..10000166145.90 rows=4017625 width=16) (actual time=0.003..392.535 rows=1963386 loops=1)"
"Total runtime: 768.473 ms"

Индекс в столбце отметки времени не используется. Как оптимизировать этот запрос?


postgresql sql between indexing postgresql-performance
person Nko    schedule 16.04.2013    source источник
comment
Вы отключали параметры планировщика? Затраты cost=10000000000.00 выглядят ужасно подозрительно.   -  person Richard Huxton    schedule 16.04.2013
comment
Как я могу установить значение по умолчанию для параметров планировщика?   -  person Nko    schedule 16.04.2013
comment
Я подумал, что вы, возможно, отключили один или несколько из них: postgresql.org/docs/9.2/static/   -  person Richard Huxton    schedule 16.04.2013

Ответы (2)


arrow_upward
3
arrow_downward

Запрос выполняется менее чем за одну секунду. Остальные 6+ секунд тратятся на трафик между сервером и клиентом.

person Clodoaldo Neto    schedule 16.04.2013
comment
Спасибо, Нето, хорошо, эффективное время выполнения запроса меньше одной секунды. Я увижу эту задержку в трафике между клиентом и сервером, когда тоже буду разрабатывать свое решение? - person Nko; 16.04.2013
comment
@Нко Конечно. Наверное хуже. Вам нужно 1,7 миллиона строк? - person Clodoaldo Neto; 16.04.2013
comment
@Nko... Нето прав. На данный момент вы не получите многого от оптимизации предложения даты. Количество записей, которые вы извлекаете, является основной проблемой. - person marcj; 16.04.2013
comment
Хорошо, спасибо, это был только мой первый подход к такого рода данным, конечно, мне нужно агрегировать эти полученные данные, минимизируя таким образом общее время выполнения. Спасибо - person Nko; 16.04.2013

arrow_upward
5
arrow_downward

Правильный DDL-скрипт

Не уверен, какую нотацию вы используете в своем вопросе. Это не синтаксис Postgres. Правильная настройка может выглядеть следующим образом:
SQL Fiddle.

Подробнее об этой скрипте ниже.
Предположим, что тип данных timestamp< /a> для столбца datetime.

Неверный запрос

BETWEEN почти всегда неправильно в принципале с timestamp столбцами. Более подробная информация в этом связанном ответе:

В вашем запросе:

SELECT o.one_id, date(o.cut_time), o.f1, t.f2 
FROM   one o
JOIN   two t USING (one_id)
WHERE  o.cut_time BETWEEN '2013-01-01' AND '2013-01-31';

... строковые константы «2013-01-01» и «2013-01-31» приводятся к отметкам времени «2013-01-01 00:00» и «2013-01-31 00:00». Это исключает большую часть 31 января. Отметка времени "2013-01-31 12:00" не подходит, что, безусловно, неверно.
Если вы используете "2013-02 -01» в качестве верхней границы вместо этого будет включать «2013-02-01 00:00». Все еще неправильно.

Чтобы получить все метки времени за январь 2013 г., должно быть:

SELECT o.one_id, date(o.cut_time), o.f1, t.f2 
FROM   one o
JOIN   two t USING (one_id)
WHERE  o.cut_time >= '2013-01-01'
AND    o.cut_time <  '2013-02-01';

Исключить верхнюю границу.

Оптимизировать запрос

@Clodoaldo уже упомянул о серьезном снижении производительности: вероятно, бессмысленно извлекать 1,7 миллиона строк. Агрегируйте перед получением результата.

Поскольку таблица two намного больше, ключевыми являются строки, которые вы должны получить оттуда. Пока вы извлекаете большую часть таблицы, более ~ 5% , простой индекс по two.one_id не будет использоваться, потому что быстрее последовательно сканировать таблицу сразу.

Статистика вашей таблицы устарела, или вы перепутали константы стоимости и другие параметры (которые, очевидно, у вас есть, см. ниже), чтобы заставить Postgres в любом случае использовать индекс.

Единственный шанс, который я вижу для индекса two, — это покрывающий индекс с PostgreSQL 9.2. Но вы забыли раскрыть номер своей версии.

CREATE INDEX two_one_id_f2 on two(one_id, f2);

Таким образом, Postgres может напрямую читать из индекса, если выполняются некоторые предварительные условия. Может чуть быстрее, но ненамного. Не тестировал.

Странные числа в выводе EXPLAIN

Что касается ваших странных цифр в вашем EXPLAIN ANALYZE. Этот SQL Fiddle должен объяснить это.

Похоже, у вас были следующие настройки отладки:

SET enable_seqscan = off;
SET enable_indexscan = off;
SET enable_bitmapscan = off;

Все они должны быть on, кроме отладки. Подорвет производительность! Проверить с:

SELECT * FROM pg_settings WHERE name ~~ 'enable%'
person Erwin Brandstetter    schedule 16.04.2013
comment
Хороший улов на BETWEEN с timestsamp и действительно хороший, информативный пост. Я кое-что узнал :) - person marcj; 17.04.2013
comment
Спасибо, Эрвин, за подсказку с указателем покрытия. Извините, я пропустил информацию о том, что использую Postgresql 9.2. - person Nko; 17.04.2013