PostgreSQL 9.4.1 on x86_64-pc-linux-gnu

psql Table "statistics.company_statistic_pages_by_months_15_03"
Column | Type | Modifiers | Storage | Stats target | Description
------------+------------------------+-------------------------------------------------------------------------------------------+----------+--------------+-------------
id | bigint | not null default nextval('statistics.company_statistic_pages_by_months_id_seq'::regclass) | plain | |
company_id | integer | | plain | |
date | date | not null | plain | |
page | character varying(200) | not null | extended | |
pages | integer | not null default 0 | plain | |
Indexes:
"s_c_stat_pages_by_months_15_03_company_id_date_idx" btree (company_id, date)
Check constraints:
"company_statistic_pages_by_months_15_03_date_check" CHECK (date >= '2015-03-01 00:00:00'::timestamp without time zone AND date < '2015-04-01 00:00:00'::timestamp without time zone)
Inherits: statistics.company_statistic_pages_by_months

psqEXPLAIN ANALYZE SELECT company_id, date, sum(pages) FROM statistics.company_statistic_pages_by_months_15_03 WHERE (date >= '2015-03-01') AND company_id = (99256128) GROUP BY company_id, date;

HashAggregate (cost=3798.63..3798.64 rows=1 width=12) (actual time=4.283..4.284 rows=1 loops=1)
-> Bitmap Heap Scan on company_statistic_pages_by_months_15_03 (cost=22.43..3791.31 rows=975 width=12) (actual time=0.319..3.024 rows=2404 loops=1)
Recheck Cond: ((company_id = 99256128) AND (date >= '2015-03-01'::date))
-> Bitmap Index Scan on s_c_stat_pages_by_months_15_03_company_id_date_idx (cost=0.00..22.19 rows=975 width=0) (actual time=0.319..0.319 rows=2404 loops=1)
Index Cond: ((company_id = 99256128) AND (date >= '2015-03-01'::date))
Total runtime: 4.292 ms
(6 rows)
VS
psqEXPLAIN ANALYZE SELECT company_id, date, sum(pages) FROM statistics.company_statistic_pages_by_months_15_03 WHERE (date = '2015-03-01') AND company_id = (99256128) GROUP BY company_id, date;

GroupAggregate (cost=22.43..3798.64 rows=1 width=12) (actual time=5.818..5.819 rows=1 loops=1)
-> Bitmap Heap Scan on company_statistic_pages_by_months_15_03 (cost=22.43..3791.31 rows=975 width=12) (actual time=0.908..4.612 rows=2404 loops=1)
Recheck Cond: ((company_id = 99256128) AND (date = '2015-03-01'::date))
-> Bitmap Index Scan on s_c_stat_pages_by_months_15_03_company_id_date_idx (cost=0.00..22.19 rows=975 width=0) (actual time=0.430..0.430 rows=2404 loops=1)
Index Cond: ((company_id = 99256128) AND (date = '2015-03-01'::date))
Total runtime: 5.827 ms
(6 rows)


Я не понимаю в чем разница для оптимизатора и в реальной стоимости.
Почему оптимизатор имея чек не оптимизирует условие ?
И это не только не верная стоимость в плане, первый запрос выполняется сильно дольше. Это упрощенный запрос, тут одна партиция, в реальности их много и запрос по диапазону, а по факту по нескольким фикс. датам.

Простая оптимизация date >= date1 and date <= date2 -> date in (date1, date2) дает хороший рост перфоманса. почему если есть чек ?

Изменение чеков, типов, приведение типов, не помогает.

Второй вопрос, почему при сканировании каждой партиции Pg не переписывает условие с учетом наличия чека. Первого или второго было бы достаточно.

про constraint_exclusion, он работает.

вопрос почему при наличии чек констрейта на таблице date = '2015-01-01'::date,
при запросе есть разница в плане и в реальной стоимости и времени выполнения
между
date = '2015-01-01'::date
и
date >= '2015-01-01'6::date

?

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

чем реальная работа отличается в первом и втором случае?


версию кстати не правильно указал
PostgreSQL 9.3.5 on x86_64-pc-linux-gnu
на 9.4.1 так же.

Раз всем не понятно, что я хочу, значит плохо объясняю. Подготовлю данные и напишу нормальный пример.

psql\d+ statistics.banner_total_by_months;
Table "statistics.banner_total_by_months"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
-----------+---------+--------------------------------------------------------------------------------+---------+--------------+---------
----
id | integer | not null default nextval('statistics.banner_total_by_months_id_seq'::regclass) | plain | |
date | date | not null | plain | |
banner_id | integer | not null | plain | |
rubric_id | integer | not null | plain | |
region_id | integer | not null | plain | |
hits | integer | not null default 0 | plain | |
views | integer | not null default 0 | plain | |
Indexes:
"banner_total_by_months_pkey" PRIMARY KEY, btree (id)
"stat_b_t_m_date_banner_id_rubric_id_region_id" UNIQUE, btree (date, banner_id, rubric_id, region_id)
Child tables: statistics.banner_total_by_months_p2015_01,
statistics.banner_total_by_months_p2015_02,
statistics.banner_total_by_months_p2015_03,
statistics.banner_total_by_months_p2015_04,
statistics.banner_total_by_months_p2015_05,
statistics.banner_total_by_months_p2015_06,
statistics.banner_total_by_months_p2015_07,
statistics.banner_total_by_months_p2015_08,
statistics.banner_total_by_months_p2015_09

\d+ statistics.banner_total_by_months_p2015_07;; Table "statistics.banner_total_by_months_p2015_07"
Column | Type | Modifiers | Storage | Stats target | Descript
ion
-----------+---------+--------------------------------------------------------------------------------+---------+--------------+---------
----
id | integer | not null default nextval('statistics.banner_total_by_months_id_seq'::regclass) | plain | |
date | date | not null | plain | |
banner_id | integer | not null | plain | |
rubric_id | integer | not null | plain | |
region_id | integer | not null | plain | |
hits | integer | not null default 0 | plain | |
views | integer | not null default 0 | plain | |
Indexes:
"banner_total_by_months_p2015_07_pkey" PRIMARY KEY, btree (id)
"banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx6" UNIQUE, btree (date, banner_id, rubric_id, region_id)
Check constraints:
"banner_total_by_months_p2015_07_partition_check" CHECK (date = '2015-07-01'::date)
Inherits: statistics.banner_total_by_months



psqlEXPLAIN ANALYZE SELECT banner_id, date, sum(hits) AS hits, sum(views) AS views FROM statistics.banner_total_by_months
WHERE (date >= '2015-01-01' AND date <= '2015-12-31') AND banner_id = (489)
GROUP BY banner_id, date;

HashAggregate (cost=233385.04..233389.31 rows=427 width=16) (actual time=1606.323..1606.327 rows=3 loops=1)
Group Key: banner_total_by_months.banner_id, banner_total_by_months.date
-> Append (cost=0.00..233354.07 rows=3097 width=16) (actual time=517.898..1605.974 rows=184 loops=1)
-> Seq Scan on banner_total_by_months (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_regi_idx on banner_total_by_months_p2015_01 (cost=0
.15..4.25 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx1 on banner_total_by_months_p2015_02 (cost=0
.15..4.25 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx2 on banner_total_by_months_p2015_03 (cost=0
.15..4.25 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx3 on banner_total_by_months_p2015_04 (cost=0
.42..4892.27 rows=155 width=16) (actual time=104.282..104.282 rows=0 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx4 on banner_total_by_months_p2015_05 (cost=0
.42..5213.08 rows=126 width=16) (actual time=46.024..46.024 rows=0 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx5 on banner_total_by_months_p2015_06 (cost=0
.43..36722.18 rows=650 width=16) (actual time=367.576..393.190 rows=1 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx6 on banner_total_by_months_p2015_07 (cost=0
.43..159583.77 rows=1808 width=16) (actual time=323.256..945.077 rows=139 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx7 on banner_total_by_months_p2015_08 (cost=0
.43..26925.77 rows=353 width=16) (actual time=51.211..117.297 rows=44 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx8 on banner_total_by_months_p2015_09 (cost=0
.15..4.25 rows=1 width=16) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((date >= '2015-01-01'::date) AND (date <= '2015-12-31'::date) AND (banner_id = 489))
Planning time: 2.796 ms
Execution time: 1606.561 ms
(25 rows)


VS

psql EXPLAIN ANALYZE SELECT banner_id, date, sum(hits) AS hits, sum(views) AS views FROM statistics.banner_total_by_months
WHERE (date in ('2015-01-01', '2015-02-01', '2015-03-01', '2015-04-01', '2015-05-01', '2015-06-01', '2015-07-01', '2015-08-01', '2015-09-01', '2015-10-01', '2015-11-01', '2015-12-01')) AND banner_id = (489)
GROUP BY banner_id, date;

HashAggregate (cost=2474.67..2479.17 rows=450 width=16) (actual time=31.492..31.494 rows=3 loops=1)
Group Key: banner_total_by_months.banner_id, banner_total_by_months.date
-> Append (cost=0.00..2449.36 rows=2531 width=16) (actual time=14.939..31.314 rows=184 loops=1)
-> Seq Scan on banner_total_by_months (cost=0.00..0.00 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)
Filter: ((banner_id = 489) AND (date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01
,2015-08-01,2015-09-01,2015-10-01,2015-11-01,2015-12-01}'::date[])))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_regi_idx on banner_total_by_months_p2015_01 (cost=0
.16..5.97 rows=1 width=16) (actual time=0.014..0.014 rows=0 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx1 on banner_total_by_months_p2015_02 (cost=0
.16..5.97 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx2 on banner_total_by_months_p2015_03 (cost=0
.16..5.97 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx3 on banner_total_by_months_p2015_04 (cost=0
.43..164.23 rows=155 width=16) (actual time=5.691..5.691 rows=0 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx4 on banner_total_by_months_p2015_05 (cost=0
.43..141.70 rows=126 width=16) (actual time=0.067..0.067 rows=0 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx5 on banner_total_by_months_p2015_06 (cost=0
.44..612.94 rows=650 width=16) (actual time=9.145..11.566 rows=1 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx6 on banner_total_by_months_p2015_07 (cost=0
.44..1165.45 rows=1242 width=16) (actual time=2.975..13.245 rows=139 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx7 on banner_total_by_months_p2015_08 (cost=0
.44..341.15 rows=353 width=16) (actual time=0.079..0.648 rows=44 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
-> Index Scan using banner_total_by_months_p2015__date_banner_id_rubric_id_reg_idx8 on banner_total_by_months_p2015_09 (cost=0
.16..5.97 rows=1 width=16) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: ((date = ANY ('{2015-01-01,2015-02-01,2015-03-01,2015-04-01,2015-05-01,2015-06-01,2015-07-01,2015-08-01,2015-0
9-01,2015-10-01,2015-11-01,2015-12-01}'::date[])) AND (banner_id = 489))
Planning time: 1.942 ms
Execution time: 31.746 ms


вопросы:
- чем по выполняемой работе, запрос 1 отличается от запроса 2 ?
- почему Pg сам не оптимизирует запрос 1 до 2 при наличии чек констрейтов на партициях, которые говорят что в таблице данные только за одну дату?
тоже самое происходит при запросе к конкретной партиции по простому условию, запрос = '2015-07-01'::date сильно отличается от >= '2015-07-01'::date, хотя результат один и при наличии чек констрейта я ожидаю, что оптимизатор упростит выражение до =.

Я как понимаю он не умеет такую оптимизацию делать. Но чем реально по работе запрос 1 отличается от запроса 2?

чуть порезал условия, выполнил несколько раз, выбрал самый быстрый вариант.




взят самый медленный результат, быстрый 2 мс

Спасибо всем

добавили доп индекс по баннер_ид, регион_ид пока, будем смотреть. Основной индекс не меняли по некоторым причинам: констрейт изменить затруднительно их создает partman (библ. для партиционирования), сложно менять уникальный индекс на куче партиций, может позже сделаю. я понимаю что этот индекс избыточен.

по поводу: set enable_mergejoin = off
попробовал, но видимо после первого выполнения запроса все закешировалось и все быстро было и планинг и выполнение, ничего нового в плане не увидел. план сохранил. если сильно надо могу еще попробовать.