Гость
Войти | Профиль | Очистить
Действия ...
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Какой использовать индекс за столбцов с низким кардиналити? / 17 сообщений из 17, страница 1 из 1
04.06.2018, 11:36
    #39654758
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Здрасте, уважаемые!

Есть таблица, достаточно широкая и с ~10 миллионами записей (чисто тестовые данные, будет больше).
В ней есть столбец с очень низким кардиналити - порядка 80 вариантов на эти самые 10 лямов. Только инты.

При навешивании простого btree уменьшение веса селекта падает с 570к до 420к. Недостаточно и понимаю, что дереву тут не место.
Но битмапы, как я понял, в постгре нету.

Вопрос: какой индекс стоит использовать в таком случае?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 11:39
    #39654762
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Забыл добавить:
Постгря 10.
Партицирование не предлагать - таблица уже партицирована.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 11:44
    #39654767
jan2ary
jan2ary
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Johnson1893,

А как партиционирована? Как распределены данные?
Что вы хотите от индекса при такой кардинальности? Если надо с индексом прочитать 90% партиции, то лучше читать саму таблицу последовательно. У вас олтп, или аналитика?
Что с параллелью?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 12:18
    #39654793
Щукина Анна
Щукина Анна
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Johnson1893,

репартицируйте вашу таблицу. первично (partition) по низкокардинальному полю, вторично (sub-partition) - как оно сейчас порезано на части.
ну и нужно понимать, что секционирование будет к месту лишь в том случае, если условия фильтрации в запросах ложатся на схему секционирования. Чтобы не получилось, что таблицу вы порезали на "синее", "красное", "зеленое", а искать будете "теплое", "твердое", "квадратное".
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 12:23
    #39654800
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Johnson1893Здрасте, уважаемые!

Есть таблица, достаточно широкая и с ~10 миллионами записей (чисто тестовые данные, будет больше).
В ней есть столбец с очень низким кардиналити - порядка 80 вариантов на эти самые 10 лямов. Только инты.

При навешивании простого btree уменьшение веса селекта падает с 570к до 420к. Недостаточно и понимаю, что дереву тут не место.
Но битмапы, как я понял, в постгре нету.

Вопрос: какой индекс стоит использовать в таком случае?

Вы бы лучше реальное время работы сравнивали у запросов а не цены у explain.
Вообще все индексы ставятся не для сферического коня в вакууме а для ускорения конкретных прикладных запросов.
Вот от реальных запросов нужных вам и прыгайте, при низкой селективности как у вас самое быстрое будет параллельный seq scan в 4-8 потоков скорее всего просто при выборе по этому полю (но вообще это очень странный use case когда надо выбирать ТОЛЬКО по полю с кардиналити 80).

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 12:36
    #39654811
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Партицирована по месяцам, на три года (36 партиций). Всего 6 связанных партицированных таблиц (все партицированы одинаково).
Первичная выборка идёт по месяцу (непосредственный ключ партиции), затем по полю, о котором вопрос (ид региона).
То есть она и так уже партицирована по наименьшей кардинальности.

Делать суб-партиции и получить вместо 216 партиций получить 17280 - не кажется мне охеренной идеей...

Распределение значений до регионам достаточно равномерно, то есть выборка реально вернет не 90%, а около 1/80 всех записей партиции. Аналогично равномерно для другой части партиции.

Про параллель всё просто. Её условно нет. Если вдруг она случается - оператору предусмотрено отрывание рук. Шутка, на самом деле её отсутствие будет гарантироваться бизнесом сервера. Возможно, в дальнейшем будут проверки, на исключение параллельности в БД.

Не аналитика, но достаточно сложный запрос в plsql, возвращающий массив сложных UDT в массиве либо курсоре (для моего драйвера по сути не важно).
От индекса я хочу получить достаточно быструю выборку по одному значению, по которой уже дальше пойдёт присоединение других таблиц. Ради этого готов вынести первоначальный запрос в with.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 12:42
    #39654815
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Johnson1893Партицирована по месяцам, на три года (36 партиций). Всего 6 связанных партицированных таблиц (все партицированы одинаково).
Первичная выборка идёт по месяцу (непосредственный ключ партиции), затем по полю, о котором вопрос (ид региона).
То есть она и так уже партицирована по наименьшей кардинальности.

Делать суб-партиции и получить вместо 216 партиций получить 17280 - не кажется мне охеренной идеей...

Распределение значений до регионам достаточно равномерно, то есть выборка реально вернет не 90%, а около 1/80 всех записей партиции. Аналогично равномерно для другой части партиции.

Про параллель всё просто. Её условно нет. Если вдруг она случается - оператору предусмотрено отрывание рук. Шутка, на самом деле её отсутствие будет гарантироваться бизнесом сервера. Возможно, в дальнейшем будут проверки, на исключение параллельности в БД.

Не аналитика, но достаточно сложный запрос в plsql, возвращающий массив сложных UDT в массиве либо курсоре (для моего драйвера по сути не важно).
От индекса я хочу получить достаточно быструю выборку по одному значению, по которой уже дальше пойдёт присоединение других таблиц. Ради этого готов вынести первоначальный запрос в with.

Параллельность я имел в виду параллельное выполнение запросов в базе силами нескольких ядер. При вашей кардинальности это скорее всего будет быстрее чем index или bitmap scan.
А вообще показывайте запросы, показывайте результаты explain (analyze, costs, buffers, timing)
WITH в такой ситуации вам только все замедлит.
Вернуть 1/80 таблицы с наибольшей вероятностью будет быстрее именно так.

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 12:45
    #39654820
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Maxim Boguk,

Без индекса
[2018-06-04 14:40:06] 500 rows retrieved starting from 1 in 2 s 154 ms (execution: 2 s 81 ms, fetching: 73 ms)
С индексом
[2018-06-04 14:40:38] 500 rows retrieved starting from 1 in 164 ms (execution: 94 ms, fetching: 70 ms)
Снова без индекса
[2018-06-04 14:41:42] 500 rows retrieved starting from 1 in 2 s 134 ms (execution: 2 s 80 ms, fetching: 54 ms)

Да, выглядит впечатляюще, чёт я не подумал. Нужно, конечно, еще джоинами обмазаться и построением UDT (что ведет необходимость группировки), но в целом я удивлен, что планировка так ошиблась...
Спасибо за совет!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 12:51
    #39654825
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Maxim Boguk,

На цифры особо смотреть не стоит, это только первая часть запроса... Дальше будет присоединен десяток таблиц, будет группировка и создание UDT. В целом существующий сейчас запрос (регионы в одной из присоединенных таблиц) просто сосёт по скорости. Вот пытаюсь как-то это дело прооптимизировать. Могу существующий запрос приложить, если нужно, чтобы имели представление, что за монстр там собирается...

Запрос:
Код: sql
1.
2.
3.
4.
5.
SELECT *
FROM smfd_data.t_bill_base bb -- partition base
WHERE bb.period_id = 2 -- partition key
	  AND bb.region_id = 66 -- need index here?
	  AND bb.email IS NOT NULL;



Эксплейн без индекса:
постгряGather (cost=1000.00..527903.61 rows=27370 width=228) (actual time=179.305..1034.805 rows=32517 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=273387
-> Append (cost=0.00..524166.61 rows=11404 width=228) (actual time=434.287..1008.551 rows=10839 loops=3)
Buffers: shared hit=470488
-> Parallel Seq Scan on t_bill_2018_06 bb (cost=0.00..524166.61 rows=11404 width=228) (actual time=434.286..1007.756 rows=10839 loops=3)
Filter: ((email IS NOT NULL) AND (period_id = 2) AND (region_id = 66))
Rows Removed by Filter: 2852020
Buffers: shared hit=470488
Planning time: 0.461 ms
Execution time: 1074.754 ms


Эксплейн с индексом:
постгряAppend (cost=4895.24..424426.19 rows=27370 width=228) (actual time=39.549..156.154 rows=32517 loops=1)
Buffers: shared hit=14255 read=732
I/O Timings: read=4.719
-> Bitmap Heap Scan on t_bill_2018_06 bb (cost=4895.24..424426.19 rows=27370 width=228) (actual time=39.548..152.745 rows=32517 loops=1)
Recheck Cond: (region_id = 66)
Filter: ((email IS NOT NULL) AND (period_id = 2))
Rows Removed by Filter: 234109
Heap Blocks: exact=14255
Buffers: shared hit=14255 read=732
I/O Timings: read=4.719
-> Bitmap Index Scan on t_bill_2018_06_region_id_index (cost=0.00..4888.40 rows=264528 width=0) (actual time=35.368..35.368 rows=266626 loops=1)
Index Cond: (region_id = 66)
Buffers: shared read=732
I/O Timings: read=4.719
Planning time: 0.932 ms
Execution time: 158.062 ms
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 13:00
    #39654835
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Вообще, сейчас вот, используется такая портянка. Код с моими комментами и малость причесанный по именам переменных и стилю:

 
осторожно, может вызвать приступ гнева
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
EXPLAIN ( ANALYSE, BUFFERS )

WITH -- Адреса, только с указанным регионом
		address_objects AS (
		SELECT
			addr_inner.id                            id,
			(addr_inner.zip_code,
			 addr_inner.region_id,
			 addr_inner.city_type,
			 addr_inner.city,
			 addr_inner.street_type,
			 addr_inner.street,
			 addr_inner.house,
			 addr_inner.corpus,
			 addr_inner.flat) :: smfd_data.S_ADDRESS addr_object
		FROM smfd_data.t_address addr_inner
		WHERE addr_inner.region_id = 66
	),
	--- Абоненты
		abonent_objects AS (
		SELECT
			abon.id                  id,
			(abon.full_name,
			 abon.abonent_type,
			 addr.addr_object,
			 abon.abonent_uniq_code,
			 abon.contact_phone
			) :: smfd_data.S_ABONENT abon_object
		FROM address_objects addr
			JOIN smfd_data.t_bill_abonent abon ON abon.address = addr.id
	),
	--- Лицевые счета с их дополнительными таблицами
		account_records AS (
		SELECT
			(acc.account_number,
			 abon.abon_object,
			 -- region details
			 array_agg((
						   bill_details.service_number,
						   bill_details.service_type,
						   bill_details.detail_name,
						   bill_details.priority_order,
						   bill_details.quantity,
						   bill_details.quantity_unit,
						   bill_details.detail_sum
					   ) :: smfd_data.S_BILL_DETAILS) :: smfd_data.S_BILL_DETAILS [],
			 -- endregion details
			 -- region calls
			 array_agg((
						   call_details.service_number,
						   call_details.tariff_name,
						   call_details.stat_date,
						   call_details.service_subtype,
						   call_details.vendor_id,
						   call_details.connect_type,
						   call_details.connect_period,
						   call_details.connect_cost,
						   call_details.connect_code
					   ) :: smfd_data.S_BILL_CALL_DETAILS) :: smfd_data.S_BILL_CALL_DETAILS []
			 -- endregion calls
			) :: smfd_data.S_ACCOUNT acc_object,
			acc.bill_id              acc_bill
		FROM abonent_objects abon
			JOIN smfd_data.t_bill_accounts_base acc ON acc.abonent_id = abon.id
			JOIN smfd_data.t_bill_details_base bill_details ON bill_details.account_id = acc.id
			JOIN smfd_data.t_bill_call_details_base call_details ON call_details.account_id = acc.id
		WHERE acc.period_id = 2
		GROUP BY acc.account_number, acc.bill_id,
			abon.abon_object
	),
	--- Подключение биллов. Самая тяжелая часть запроса, около 90% веса, из-за дикой сортировки в плане (из-за группы по куче полей).
	--- todo надо бы придумать как избежать группировки сильной...
		bill_object AS (
		SELECT (
				   bill_raw.number,
				   bill_raw.bill_date,
				   bill_raw.target_date,
				   bill_raw.deadline_date,
				   bill_raw.total_pay,
				   bill_raw.total_pay_recommended,
				   array_agg((
								 bill_pay.pay_type,
								 bill_pay.pay_line_name,
								 bill_pay.pay_saldo,
								 bill_pay.pay_income,
								 bill_pay.pay_invoice,
								 bill_pay.pay_total
							 ) :: smfd_data.S_BILL_PAY) :: smfd_data.S_BILL_PAY [],
				   bill_raw.qr_code,
				   bill_raw.barcode_common,
				   bill_raw.barcode_recommended,
				   array_agg(acc.acc_object) :: smfd_data.S_ACCOUNT [],
				   (
					   vendor.vendor_name,
					   vendor.call_center_phone,
					   vendor.address_string,
					   vendor.jur_info_string,
					   vendor.jur_info_rs,
					   vendor.jur_info_ks,
					   vendor.jur_info_inn,
					   vendor.jur_info_bank_name,
					   vendor.jur_info_bic
				   ) :: smfd_data.S_VENDOR,
				   array_agg((params.param_name, params.param_value) :: S_KEY_VALUE_PAIR) :: S_KEY_VALUE_PAIR [],
				   bill_raw.history_entry,
				   bill_raw.delivery_type,
				   bill_raw.email
			   ) :: smfd_data.S_BILL bill
		FROM account_records acc
			JOIN smfd_data.t_bill_base bill_raw ON bill_raw.id = acc.acc_bill
			JOIN smfd_data.t_vendors vendor ON vendor.id = bill_raw.vendor
			JOIN smfd_data.t_bill_additional_params_base params ON params.bill_id = bill_raw.id
			JOIN smfd_data.t_bill_pay_base bill_pay ON bill_pay.bill_id = bill_raw.id
		WHERE bill_raw.email IS NOT NULL
		GROUP BY
			-- Мне очень не нравится такая группировка. Она вызывает сортировку по куче полей с огромным весом планировки.
			bill_raw.id, bill_raw.number, bill_raw.bill_date, bill_raw.target_date, bill_raw.deadline_date,
			bill_raw.total_pay, bill_raw.total_pay_recommended, bill_raw.qr_code, bill_raw.barcode_common, bill_raw.barcode_recommended,
			bill_raw.history_entry, bill_raw.delivery_type, bill_raw.email,
			vendor.vendor_name, vendor.call_center_phone, vendor.address_string, vendor.jur_info_string,
			vendor.jur_info_rs, vendor.jur_info_ks, vendor.jur_info_inn, vendor.jur_info_bank_name, vendor.jur_info_bic
		LIMIT 30
	)
SELECT *
FROM bill_object;



Выдает такой вот план:

 
explane
[2018-06-04 14:58:58] 152 rows retrieved starting from 1 in 1 m 33 s 955 ms (execution: 1 m 33 s 931 ms, fetching: 24 ms)
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
CTE Scan on bill_object  (cost=5020564203297283.00..5020564203297284.00 rows=30 width=32) (actual time=93502.850..93526.157 rows=30 loops=1)
  Buffers: shared hit=23323905 read=1311406, temp read=627969 written=770655
  I/O Timings: read=3474.670
  CTE address_objects
    ->  Gather  (cost=1000.00..167993.10 rows=282885 width=36) (actual time=0.920..578.561 rows=263586 loops=1)
          Workers Planned: 2
          Workers Launched: 2
          Buffers: shared hit=38251 read=9578
          I/O Timings: read=30.486
          ->  Parallel Seq Scan on t_address addr_inner  (cost=0.00..138704.60 rows=117869 width=36) (actual time=0.046..491.757 rows=87862 loops=3)
                Filter: (region_id = 66)
                Rows Removed by Filter: 2441883
                Buffers: shared hit=78441 read=20728
                I/O Timings: read=122.336
  CTE abonent_objects
    ->  Hash Join  (cost=416810.54..1046145.41 rows=699226 width=36) (actual time=4465.705..6758.634 rows=266626 loops=1)
          Hash Cond: (addr.id = abon.address)
          Buffers: shared hit=113524 read=50670, temp read=91143 written=91129
          I/O Timings: read=175.009
          ->  CTE Scan on address_objects addr  (cost=0.00..5657.70 rows=282885 width=36) (actual time=0.924..666.469 rows=263586 loops=1)
                Buffers: shared hit=38251 read=9578
                I/O Timings: read=30.486
          ->  Hash  (cost=201730.13..201730.13 rows=8536513 width=73) (actual time=4458.969..4458.969 rows=8533868 loops=1)
                Buckets: 4194304  Batches: 8  Memory Usage: 145764kB
                Buffers: shared hit=75273 read=41092, temp written=87885
                I/O Timings: read=144.524
                ->  Seq Scan on t_bill_abonent abon  (cost=0.00..201730.13 rows=8536513 width=73) (actual time=0.025..1887.318 rows=8533868 loops=1)
                      Buffers: shared hit=75273 read=41092
                      I/O Timings: read=144.524
  CTE account_records
    ->  GroupAggregate  (cost=214524145816.29..220892412538.06 rows=1717672200 width=81) (actual time=53325.124..58312.220 rows=51253 loops=1)
          Group Key: acc.account_number, acc.bill_id, abon_1.abon_object
          Buffers: shared hit=155235 read=1219178, temp read=571698 written=571675
          I/O Timings: read=3115.885
          ->  Sort  (cost=214524145816.29..215430217478.97 rows=362428665073 width=285) (actual time=53324.869..55157.722 rows=1496946 loops=1)
                Sort Key: acc.account_number, acc.bill_id, abon_1.abon_object
                Sort Method: external merge  Disk: 644680kB
                Buffers: shared hit=155235 read=1219178, temp read=571698 written=571675
                I/O Timings: read=3115.885
                ->  Hash Join  (cost=1637581.42..3751796.49 rows=362428665073 width=285) (actual time=25875.150..45973.178 rows=1496946 loops=1)
                      Hash Cond: (bill_details.account_id = acc.id)
                      Buffers: shared hit=155235 read=1219178, temp read=491113 written=491087
                      I/O Timings: read=3115.885
                      ->  Append  (cost=0.00..808736.13 rows=26629113 width=124) (actual time=0.030..8818.018 rows=26613278 loops=1)
                            Buffers: shared read=542445
                            I/O Timings: read=1431.904
                            ->  Seq Scan on t_bill_details_2018_06 bill_details  (cost=0.00..808736.13 rows=26629113 width=124) (actual time=0.030..6949.845 rows=26613278 loops=1)
                                  Buffers: shared read=542445
                                  I/O Timings: read=1431.904
                      ->  Hash  (cost=1537098.81..1537098.81 rows=2722049 width=173) (actual time=25872.209..25872.209 rows=420823 loops=1)
                            Buckets: 2097152  Batches: 4  Memory Usage: 50555kB
                            Buffers: shared hit=155235 read=676733, temp read=133800 written=146090
                            I/O Timings: read=1683.981
                            ->  Hash Join  (cost=445765.49..1537098.81 rows=2722049 width=173) (actual time=12211.821..25612.987 rows=420823 loops=1)
                                  Hash Cond: (call_details.account_id = acc.id)
                                  Buffers: shared hit=155235 read=676733, temp read=133800 written=133780
                                  I/O Timings: read=1683.981
                                  ->  Append  (cost=0.00..938735.24 rows=33434024 width=120) (actual time=0.040..10696.773 rows=33488662 loops=1)
                                        Buffers: shared read=604395
                                        I/O Timings: read=1432.224
                                        ->  Seq Scan on t_bill_call_details_2018_06 call_details  (cost=0.00..938735.24 rows=33434024 width=120) (actual time=0.040..8452.256 rows=33488662 loops=1)
                                              Buffers: shared read=604395
                                              I/O Timings: read=1432.224
                                  ->  Hash  (cost=437025.16..437025.16 rows=699226 width=53) (actual time=12210.464..12210.464 rows=266626 loops=1)
                                        Buckets: 1048576  Batches: 1  Memory Usage: 68190kB
                                        Buffers: shared hit=155235 read=72338, temp read=133800 written=133780
                                        I/O Timings: read=251.757
                                        ->  Hash Join  (cost=336798.03..437025.16 rows=699226 width=53) (actual time=8516.904..12105.001 rows=266626 loops=1)
                                              Hash Cond: (abon_1.id = acc.abonent_id)
                                              Buffers: shared hit=155235 read=72338, temp read=133800 written=133780
                                              I/O Timings: read=251.757
                                              ->  CTE Scan on abonent_objects abon_1  (cost=0.00..13984.52 rows=699226 width=36) (actual time=4465.709..6865.660 rows=266626 loops=1)
                                                    Buffers: shared hit=113524 read=50670, temp read=91143 written=91129
                                                    I/O Timings: read=175.009
                                              ->  Hash  (cost=170733.51..170733.51 rows=8588361 width=25) (actual time=4046.825..4046.825 rows=8588577 loops=1)
                                                    Buckets: 4194304  Batches: 4  Memory Usage: 158657kB
                                                    Buffers: shared hit=41711 read=21668, temp written=37733
                                                    I/O Timings: read=76.748
                                                    ->  Append  (cost=0.00..170733.51 rows=8588361 width=25) (actual time=0.038..2213.983 rows=8588577 loops=1)
                                                          Buffers: shared hit=41711 read=21668
                                                          I/O Timings: read=76.748
                                                          ->  Seq Scan on t_bill_accounts_2018_06 acc  (cost=0.00..170733.51 rows=8588361 width=25) (actual time=0.037..1611.217 rows=8588577 loops=1)
                                                                Filter: (period_id = 2)
                                                                Buffers: shared hit=41711 read=21668
                                                                I/O Timings: read=76.748
  CTE bill_object
    ->  Limit  (cost=5020342644917717.00..5020343309670606.00 rows=30 width=2608) (actual time=93502.683..93517.873 rows=30 loops=1)
          Buffers: shared hit=23323905 read=1311406, temp read=627969 written=770655
          I/O Timings: read=3474.670
          ->  GroupAggregate  (cost=5020342644917717.00..5085321774495411.00 rows=2932479 width=2608) (actual time=93502.681..93517.863 rows=30 loops=1)
                Group Key: bill_raw.id, bill_raw.number, bill_raw.bill_date, bill_raw.target_date, bill_raw.deadline_date, bill_raw.total_pay, bill_raw.total_pay_recommended, bill_raw.qr_code, bill_raw.barcode_common, bill_raw.barcode_recommended, bill_raw.history_entry, bill_raw.delivery_type, bill_raw.email, vendor.vendor_name, vendor.call_center_phone, vendor.address_string, vendor.jur_info_string, vendor.jur_info_rs, vendor.jur_info_ks, vendor.jur_info_inn, vendor.jur_info_bank_name, vendor.jur_info_bic
                Buffers: shared hit=23323905 read=1311406, temp read=627969 written=770655
                I/O Timings: read=3474.670
                ->  Sort  (cost=5020342644917717.00..5022841842206629.00 rows=999678915564819 width=2753) (actual time=93502.201..93507.777 rows=685 loops=1)
                      Sort Key: bill_raw.id, bill_raw.number, bill_raw.bill_date, bill_raw.target_date, bill_raw.deadline_date, bill_raw.total_pay, bill_raw.total_pay_recommended, bill_raw.qr_code, bill_raw.barcode_common, bill_raw.barcode_recommended, bill_raw.history_entry, bill_raw.delivery_type, bill_raw.email, vendor.vendor_name, vendor.call_center_phone, vendor.address_string, vendor.jur_info_string, vendor.jur_info_rs, vendor.jur_info_ks, vendor.jur_info_inn, vendor.jur_info_bank_name, vendor.jur_info_bic
                      Sort Method: external sort  Disk: 715256kB
                      Buffers: shared hit=23323905 read=1311406, temp read=627969 written=770655
                      I/O Timings: read=3474.670
                      ->  Merge Join  (cost=344793134.39..14995509558606.03 rows=999678915564819 width=2753) (actual time=91548.834..92022.760 rows=70554 loops=1)
                            Merge Cond: (bill_raw.id = acc_1.acc_bill)
                            Buffers: shared hit=23323905 read=1311406, temp read=625940 written=681248
                            I/O Timings: read=3474.670
                            ->  Nested Loop  (cost=1.59..5160217.28 rows=116399043 width=2729) (actual time=44.160..30315.536 rows=26228039 loops=1)
                                  Join Filter: (bill_raw.id = params.bill_id)
                                  Buffers: shared hit=23168670 read=92228
                                  I/O Timings: read=358.785
                                  ->  Nested Loop  (cost=1.02..1408257.50 rows=339749 width=2633) (actual time=44.131..8624.088 rows=3237690 loops=1)
                                        Buffers: shared hit=5546889 read=91329
                                        I/O Timings: read=355.122
                                        ->  Nested Loop  (cost=0.58..1030601.11 rows=146624 width=2576) (actual time=44.008..4240.283 rows=881876 loops=1)
                                              Buffers: shared hit=2066869 read=22925
                                              I/O Timings: read=87.433
                                              ->  Merge Append  (cost=0.45..890660.38 rows=888632 width=220) (actual time=43.968..3012.797 rows=881876 loops=1)
                                                    Sort Key: bill_raw.id
                                                    Buffers: shared hit=303118 read=22924
                                                    I/O Timings: read=87.428
                                                    ->  Index Scan using t_bill_2018_06_id_index on t_bill_2018_06 bill_raw  (cost=0.43..883995.63 rows=888632 width=220) (actual time=43.965..2903.390 rows=881876 loops=1)
                                                          Filter: (email IS NOT NULL)
                                                          Rows Removed by Filter: 7685365
                                                          Buffers: shared hit=303118 read=22924
                                                          I/O Timings: read=87.428
                                              ->  Index Scan using t_vendors_pkey on t_vendors vendor  (cost=0.14..0.16 rows=1 width=2364) (actual time=0.001..0.001 rows=1 loops=881876)
                                                    Index Cond: (id = bill_raw.vendor)
                                                    Buffers: shared hit=1763751 read=1
                                                    I/O Timings: read=0.005
                                        ->  Append  (cost=0.44..2.33 rows=25 width=57) (actual time=0.003..0.004 rows=4 loops=881876)
                                              Buffers: shared hit=3480020 read=68404
                                              I/O Timings: read=267.688
                                              ->  Index Scan using t_bill_pay_2018_06_bill_id_pay_type_index on t_bill_pay_2018_06 bill_pay  (cost=0.44..2.33 rows=25 width=57) (actual time=0.003..0.004 rows=4 loops=881876)
                                                    Index Cond: (bill_id = bill_raw.id)
                                                    Buffers: shared hit=3480020 read=68404
                                                    I/O Timings: read=267.688
                                  ->  Append  (cost=0.57..6.76 rows=343 width=96) (actual time=0.002..0.005 rows=8 loops=3237690)
                                        Buffers: shared hit=17621781 read=899
                                        I/O Timings: read=3.664
                                        ->  Index Scan using t_bill_additional_params_2018_06_bill_id_param_name_pk on t_bill_additional_params_2018_06 params  (cost=0.57..6.76 rows=343 width=96) (actual time=0.002..0.004 rows=8 loops=3237690)
                                              Index Cond: (bill_id = bill_pay.bill_id)
                                              Buffers: shared hit=17621781 read=899
                                              I/O Timings: read=3.664
                            ->  Materialize  (cost=344793132.80..353381493.80 rows=1717672200 width=36) (actual time=59694.874..59867.623 rows=118743 loops=1)
                                  Buffers: shared hit=155235 read=1219178, temp read=625940 written=681248
                                  I/O Timings: read=3115.885
                                  ->  Sort  (cost=344793132.80..349087313.30 rows=1717672200 width=36) (actual time=59694.864..59827.008 rows=51253 loops=1)
                                        Sort Key: acc_1.acc_bill
                                        Sort Method: external merge  Disk: 433936kB
                                        Buffers: shared hit=155235 read=1219178, temp read=625940 written=681248
                                        I/O Timings: read=3115.885
                                        ->  CTE Scan on account_records acc_1  (cost=0.00..34353444.00 rows=1717672200 width=36) (actual time=53325.139..58992.400 rows=51253 loops=1)
                                              Buffers: shared hit=155235 read=1219178, temp read=571698 written=627005
                                              I/O Timings: read=3115.885
Planning time: 5.325 ms
Execution time: 93879.389 ms

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.06.2018, 22:25
    #39655241
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Johnson1893,

Для просто запроса по условию - выборка 266626 строк за 35ms - вполне приличное время и вряд ли вы его сделаете быстрее.
Общий простой запрос с допусловиями очевидно тратит время на
Filter: ((email IS NOT NULL) AND (period_id = 2))
Rows Removed by Filter: 234109

Что намекает на мысль проанализировать что у нас селективно в этом условии первая часть или вторая.
И в зависимости от этого сделать или составной индекс по (region_id, period_id) или условный индекс на (region_id) where (email IS NOT NULL).
Это скорее всего даст вам в простом запросе 3-5кратный прирост.
Быстрее вы его уже не сделаете.
Разве что попробовать 10тую версию и настроить на большее количество воркеров на запрос (но для 200000 строк это особо прироста не даст).


А вот с большим запросом надо думать.
Как минимум там очень странное дело с LIMIT без ORDER BY.
Я бы порекомендовал СНАЛАЧА в WITH сделать LIMIT без GROUP BY.
А потом уже к этим 30 строка присоединять все эти ваши остальные таблицы и делать там array_agg уже не по всей простыне а только по уже заранее подобранным 30 строкам.

--
Maxim Boguk
dataegret.ru
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2018, 06:36
    #39655309
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Maxim Boguk,

Лимит там чисто для тестов на стороне сервера. Большой объем данных для тестов там ни к чему. Лимит уйдёт.

Вопрос такой: что лучше, писать монструозный селектище с WITH, или писать простой селект по t_bill_base и дальше его циклом гонять и приселекчивать отдельно из кучи таблиц?
Еще думаю попробовать сложный селект без группировки и прямого преобразования в UDT, а уже по нему цикл и преобразование... Но надо экспериментировать...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2018, 06:43
    #39655313
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Maxim Boguk,

period_id это ключ партиции, и в выбранной партиции он всегда 2. Использовать его в индексе бессмысленно.
email is not null это уже селективный, но конкретно в этом запросе интересуют ТОЛЬКО записи с мейлом. В среднем их около 30%, равномерности никакой. Опять же, кардиналити еще ниже и смысл навешивать индекс не понятный...

Вот и мучаюсь. И чё от запроса хочу знаю, и работает всё в нескольких вариантах. А с оптимизацией беда даже на тестовой выборке...

Думал уже передавать на сервер в чистом виде и уже там собирать, но наархитектурено так, что по-простому уже не получится (объекты не плоские), а переделывать всё - просто нет времени.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
05.06.2018, 09:51
    #39655369
Maxim Boguk
Maxim Boguk
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Johnson1893Maxim Boguk,

period_id это ключ партиции, и в выбранной партиции он всегда 2. Использовать его в индексе бессмысленно.
email is not null это уже селективный, но конкретно в этом запросе интересуют ТОЛЬКО записи с мейлом. В среднем их около 30%, равномерности никакой. Опять же, кардиналити еще ниже и смысл навешивать индекс не понятный...

Вот и мучаюсь. И чё от запроса хочу знаю, и работает всё в нескольких вариантах. А с оптимизацией беда даже на тестовой выборке...

Думал уже передавать на сервер в чистом виде и уже там собирать, но наархитектурено так, что по-простому уже не получится (объекты не плоские), а переделывать всё - просто нет времени.

Так смотрите - вы хотите тяжелую аналитику по миллионам счетов. Такие вещи не в online считают а заказывают и ставят считаться в offline с получением отчета на почту.
Те цифры что вы привели в полном плане - вполне разумные учитывая сколько там строк пересчитывается.
Исходите из того что где то миллион строк в секунду можно что то сделать (select / join / etc) и прикиньте сколько там у вас всего строк обрабатывается.

По email - вам УСЛОВНЫЙ блин индекс нужен вида on(region_id) WHERE email is not null и конкретно простой приведенный вами пример оно ускорит.

Далее - прекратите WITH использовать не по делу... если у вас результат WITH подзапроса используется 1 раз - вы его не поделу используете, это оптимизационный барьер для базы (причем специально сделанный).
Как минимум первые 3 WITH - должны быть одним запросом - тогда скорее всего получится более эффективно параллельные запросы использовать.

Далее - поставьте больше параллельных workers и более дешевый их запуск в конфиге базы.

Ну и попробуйте 10 версию базы вместо 9.6 (она лучше умеет параллельное выполнение запросов).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 06:30
    #39656054
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Maxim Boguk,

Спасибо за разъяснения! Да, с кучей WITH вышло не удачно, не понимал для чего оно. Начал его использовать из-за конкретной проблемы - нельзя делать вложенную аггрегацию array_agg. "И тут Остапа понесло!".

Версия постгри и так 10.

Про условный индекс почитаю плотнее, но кажется, суть уловил, спасибо!

Задание и так заказное, через "таск менеджер". Выполняется оно в любом случае дольше, чем грузится из базы. На почту результат не получится, специфика бизнес-логики и постановки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.06.2018, 14:29
    #39656522
Ivan Durak
Ivan Durak
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
авторperiod_id это ключ партиции, и в выбранной партиции он всегда 2. Использовать его в индексе бессмысленно.
короче, давай ты просто попробуешь
index (region_id, period_id ) и покажешь exdplain
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.06.2018, 08:59
    #39657042
Johnson1893
Johnson1893
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Какой использовать индекс за столбцов с низким кардиналити?
Ivan Durak,

Сделать смогу только завтра, данные грохнули, надо заливать заново...
Пока переписал функцию, избавился вообще от WITH, правда с подзапросом и кривым джойном - не придумал как избежать вложенной аггрегации по-другому.

 
explain
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
GroupAggregate  (cost=76815.85..76815.91 rows=1 width=2684) (actual time=860.421..861.050 rows=86 loops=1)
  Group Key: bill_base.bill_id, bill_base.bill_row, bill_base.bill_pays, ven.vendor_name, ven.call_center_phone, ven.address_string, ven.jur_info_string, ven.jur_info_rs, ven.jur_info_ks, ven.jur_info_inn, ven.jur_info_bank_name, ven.jur_info_bic, bill_base.params
  Buffers: shared hit=14213 dirtied=28
  ->  Sort  (cost=76815.85..76815.86 rows=1 width=2946) (actual time=860.404..860.412 rows=86 loops=1)
        Sort Key: bill_base.bill_id, bill_base.bill_row, bill_base.bill_pays, ven.vendor_name, ven.call_center_phone, ven.address_string, ven.jur_info_string, ven.jur_info_rs, ven.jur_info_ks, ven.jur_info_inn, ven.jur_info_bank_name, ven.jur_info_bic, bill_base.params
        Sort Method: quicksort  Memory: 430kB
        Buffers: shared hit=14213 dirtied=28
        ->  Nested Loop  (cost=61619.62..76815.84 rows=1 width=2946) (actual time=767.057..860.203 rows=86 loops=1)
              Buffers: shared hit=14213 dirtied=28
              ->  Nested Loop  (cost=61619.33..76815.47 rows=1 width=2802) (actual time=767.047..859.948 rows=86 loops=1)
                    Buffers: shared hit=13955 dirtied=28
                    ->  Merge Join  (cost=61619.04..76815.09 rows=1 width=2733) (actual time=767.032..859.601 rows=86 loops=1)
                          Merge Cond: (acc.id = det_row_1.account_id)
                          Buffers: shared hit=13695 dirtied=28
                          ->  Merge Left Join  (cost=61618.61..66397.55 rows=33 width=2705) (actual time=749.899..819.915 rows=86 loops=1)
                                Merge Cond: (acc.id = det_row.account_id)
                                Buffers: shared hit=12212 dirtied=28
                                ->  Sort  (cost=806.48..806.56 rows=33 width=2673) (actual time=44.011..44.029 rows=86 loops=1)
                                      Sort Key: acc.id
                                      Sort Method: quicksort  Memory: 370kB
                                      Buffers: shared hit=2395 dirtied=28
                                      ->  Nested Loop  (cost=538.45..805.65 rows=33 width=2673) (actual time=43.332..43.819 rows=86 loops=1)
                                            Buffers: shared hit=2395 dirtied=28
                                            ->  Hash Join  (cost=538.16..549.95 rows=33 width=2652) (actual time=43.316..43.401 rows=86 loops=1)
                                                  Hash Cond: (ven.id = (bill_base.bill_row).vendor)
                                                  Buffers: shared hit=2135 dirtied=28
                                                  ->  Seq Scan on t_vendors ven  (cost=0.00..11.33 rows=33 width=2556) (actual time=0.013..0.080 rows=174 loops=1)
                                                        Buffers: shared hit=11
                                                  ->  Hash  (cost=537.72..537.72 rows=35 width=100) (actual time=43.212..43.212 rows=86 loops=1)
                                                        Buckets: 1024  Batches: 1  Memory Usage: 189kB
                                                        Buffers: shared hit=2124 dirtied=28
                                                        ->  Subquery Scan on bill_base  (cost=536.36..537.72 rows=35 width=100) (actual time=16.270..43.013 rows=86 loops=1)
                                                              Buffers: shared hit=2124 dirtied=28
                                                              ->  GroupAggregate  (cost=536.36..537.37 rows=35 width=100) (actual time=16.269..42.958 rows=86 loops=1)
                                                                    Group Key: bill.id, ((bill.*)::smfd_data.t_bill_base)
                                                                    Buffers: shared hit=2124 dirtied=28
                                                                    ->  Sort  (cost=536.36..536.46 rows=39 width=161) (actual time=15.855..16.200 rows=3184 loops=1)
                                                                          Sort Key: bill.id, ((bill.*)::smfd_data.t_bill_base)
                                                                          Sort Method: quicksort  Memory: 1651kB
                                                                          Buffers: shared hit=2124 dirtied=28
                                                                          ->  Nested Loop  (cost=0.99..535.33 rows=39 width=161) (actual time=0.554..7.666 rows=3184 loops=1)
                                                                                Join Filter: (bill.id = params.bill_id)
                                                                                Buffers: shared hit=2124 dirtied=28
                                                                                ->  Nested Loop  (cost=0.57..455.40 rows=81 width=91) (actual time=0.536..1.771 rows=344 loops=1)
                                                                                      Buffers: shared hit=328 dirtied=28
                                                                                      ->  Append  (cost=0.28..47.54 rows=35 width=36) (actual time=0.515..0.785 rows=86 loops=1)
                                                                                            Buffers: shared hit=63 dirtied=28
                                                                                            ->  Index Scan using t_bill_2018_06_region_id_index on t_bill_2018_06 bill  (cost=0.28..47.54 rows=35 width=36) (actual time=0.514..0.765 rows=86 loops=1)
                                                                                                  Index Cond: (region_id = 78)
                                                                                                  Filter: (period_id = 2)
                                                                                                  Buffers: shared hit=63 dirtied=28
                                                                                      ->  Append  (cost=0.29..11.62 rows=3 width=55) (actual time=0.006..0.009 rows=4 loops=86)
                                                                                            Buffers: shared hit=265
                                                                                            ->  Index Scan using t_bill_pay_2018_06_bill_id_pay_type_index on t_bill_pay_2018_06 bill_pay  (cost=0.29..11.62 rows=3 width=55) (actual time=0.006..0.008 rows=4 loops=86)
                                                                                                  Index Cond: (bill_id = bill.id)
                                                                                                  Filter: (period_id = 2)
                                                                                                  Buffers: shared hit=265
                                                                                ->  Append  (cost=0.42..0.86 rows=10 width=78) (actual time=0.006..0.013 rows=9 loops=344)
                                                                                      Buffers: shared hit=1796
                                                                                      ->  Index Scan using t_bill_additional_params_2018_06_bill_id_param_name_pk on t_bill_additional_params_2018_06 params  (cost=0.42..0.86 rows=10 width=78) (actual time=0.005..0.010 rows=9 loops=344)
                                                                                            Index Cond: (bill_id = bill_pay.bill_id)
                                                                                            Filter: (period_id = 2)
                                                                                            Buffers: shared hit=1796
                                            ->  Append  (cost=0.29..7.74 rows=1 width=25) (actual time=0.004..0.004 rows=1 loops=86)
                                                  Buffers: shared hit=260
                                                  ->  Index Scan using t_bill_accounts_2018_06_bill_id_index on t_bill_accounts_2018_06 acc  (cost=0.29..7.74 rows=1 width=25) (actual time=0.003..0.004 rows=1 loops=86)
                                                        Index Cond: (bill_id = bill_base.bill_id)
                                                        Filter: (period_id = 2)
                                                        Buffers: shared hit=260
                                ->  GroupAggregate  (cost=60812.13..65588.39 rows=200 width=36) (actual time=439.206..775.400 rows=2186 loops=1)
                                      Group Key: det_row.account_id
                                      Buffers: shared hit=9817
                                      ->  Sort  (cost=60812.13..62005.57 rows=477376 width=126) (actual time=439.048..443.940 rows=86105 loops=1)
                                            Sort Key: det_row.account_id
                                            Sort Method: quicksort  Memory: 115075kB
                                            Buffers: shared hit=9817
                                            ->  Append  (cost=0.00..15784.20 rows=477376 width=126) (actual time=0.010..214.238 rows=468608 loops=1)
                                                  Buffers: shared hit=9817
                                                  ->  Seq Scan on t_bill_call_details_2018_06 det_row  (cost=0.00..15784.20 rows=477376 width=126) (actual time=0.009..170.392 rows=468608 loops=1)
                                                        Filter: (period_id = 2)
                                                        Buffers: shared hit=9817
                          ->  GroupAggregate  (cost=0.43..10414.95 rows=200 width=36) (actual time=0.042..39.063 rows=3334 loops=1)
                                Group Key: det_row_1.account_id
                                Buffers: shared hit=1483
                                ->  Merge Append  (cost=0.43..9882.78 rows=105934 width=138) (actual time=0.031..8.029 rows=14367 loops=1)
                                      Sort Key: det_row_1.account_id
                                      Buffers: shared hit=1483
                                      ->  Index Scan using t_bill_details_2018_06_account_id_service_number_index on t_bill_details_2018_06 det_row_1  (cost=0.42..9088.27 rows=105934 width=138) (actual time=0.030..6.787 rows=14367 loops=1)
                                            Filter: (period_id = 2)
                                            Buffers: shared hit=1483
                    ->  Index Scan using t_bill_abonent_pkey on t_bill_abonent abonent  (cost=0.29..0.38 rows=1 width=77) (actual time=0.003..0.003 rows=1 loops=86)
                          Index Cond: (id = acc.abonent_id)
                          Buffers: shared hit=260
              ->  Index Scan using t_address_pkey on t_address address  (cost=0.29..0.37 rows=1 width=152) (actual time=0.002..0.002 rows=1 loops=86)
                    Index Cond: (id = abonent.address)
                    Buffers: shared hit=258
Planning time: 9.786 ms
Execution time: 864.586 ms



 
запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
SELECT
		bill_base.bill_id     bill_id,
		(
			(bill_base.bill_row).number,
			(bill_base.bill_row).bill_date,
			(bill_base.bill_row).target_date,
			(bill_base.bill_row).deadline_date,
			(bill_base.bill_row).total_pay,
			(bill_base.bill_row).total_pay_recommended,
			(bill_base.bill_pays),
			(bill_base.bill_row).qr_code,
			(bill_base.bill_row).barcode_common,
			(bill_base.bill_row).barcode_recommended,
			array_agg(DISTINCT (acc.account_number,
								(abonent.full_name,
								 abonent.abonent_type,
								 (address.zip_code,
								  address.region_id,
								  address.city_type,
								  address.city,
								  address.street_type,
								  address.street,
								  address.house,
								  address.corpus,
								  address.flat
								 ) :: smfd_data.S_ADDRESS,
								 abonent.abonent_uniq_code,
								 abonent.contact_phone
								) :: smfd_data.S_ABONENT,
								bill_details_record.det_obj_arr :: smfd_data.S_BILL_DETAILS [],
								call_details_record.det_obj_arr :: smfd_data.S_BILL_CALL_DETAILS []
							   ) :: smfd_data.S_ACCOUNT) :: smfd_data.S_ACCOUNT [],
			(ven.vendor_name,
			 ven.call_center_phone,
			 ven.address_string,
			 ven.jur_info_string,
			 ven.jur_info_rs,
			 ven.jur_info_ks,
			 ven.jur_info_inn,
			 ven.jur_info_bank_name,
			 ven.jur_info_bic
			) :: smfd_data.S_VENDOR,
			(bill_base.params),
			(bill_base.bill_row).history_entry,
			(bill_base.bill_row).delivery_type,
			(bill_base.bill_row).email
		) :: smfd_data.S_BILL bill
	FROM (-- Сперва грузим и фильтруем счета на оплату + их детализацию + параметры, операция достаточно быстрая.
			 SELECT
				 bill.id                                                                         bill_id,
				 bill                                                                            bill_row,
				 array_agg(DISTINCT (params.param_name, params.param_value) :: S_KEY_VALUE_PAIR) params,
				 array_agg(DISTINCT (bill_pay.pay_type,
									 bill_pay.pay_line_name,
									 bill_pay.pay_saldo,
									 bill_pay.pay_income,
									 bill_pay.pay_invoice,
									 bill_pay.pay_total) :: smfd_data.S_BILL_PAY)                bill_pays
			 FROM smfd_data.t_bill_base bill
				 JOIN smfd_data.t_bill_additional_params_base params ON params.period_id = pi_period_id AND params.bill_id = bill.id
				 JOIN smfd_data.t_bill_pay_base bill_pay ON bill_pay.period_id = pi_period_id AND bill_pay.bill_id = bill.id
			 WHERE bill.period_id = pi_period_id
				   AND bill.region_id = /* ------------------------------- */pi_region_id
				   -- todo не забыть изменить условие индекса на (bill.delivery_type = 12 /*email*/ and bill.email is not null),
				   -- todo подумать как не лососнуть на ленивом справочнике типов доставки
				   AND bill.email IS NOT NULL
			 GROUP BY bill.id, bill.*) bill_base
		JOIN smfd_data.t_vendors ven ON ven.id = (bill_base.bill_row).vendor
		JOIN smfd_data.t_bill_accounts_base acc ON acc.period_id = pi_period_id AND acc.bill_id = bill_base.bill_id
		JOIN smfd_data.t_bill_abonent abonent ON abonent.id = acc.abonent_id
		JOIN smfd_data.t_address address ON address.id = abonent.address
		-- todo на больших данных, возможно, придется MATERIALIZED VIEW делать... Чёт такая себе выборка.
		JOIN (SELECT
				  det_row.account_id                                account_id,
				  array_agg(DISTINCT (det_row.service_number,
									  det_row.service_type,
									  det_row.detail_name,
									  det_row.priority_order,
									  det_row.quantity,
									  det_row.quantity_unit,
									  det_row.detail_sum
									 ) :: smfd_data.S_BILL_DETAILS) det_obj_arr
			  FROM smfd_data.t_bill_details_base det_row
			  WHERE det_row.period_id = pi_period_id
			  GROUP BY det_row.account_id
			 ) bill_details_record ON bill_details_record.account_id = acc.id
		-- todo на больших данных, возможно, придется MATERIALIZED VIEW делать... Чёт такая себе выборка.
		LEFT JOIN (SELECT
					   det_row.account_id                                     account_id,
					   array_agg(DISTINCT (det_row.service_number,
										   det_row.tariff_name,
										   det_row.stat_date,
										   det_row.service_subtype,
										   det_row.vendor_id,
										   det_row.connect_type,
										   det_row.connect_period,
										   det_row.connect_cost,
										   det_row.connect_code
										  ) :: smfd_data.S_BILL_CALL_DETAILS) det_obj_arr
				   FROM smfd_data.t_bill_call_details_base det_row
				   WHERE det_row.period_id = pi_period_id
				   GROUP BY det_row.account_id
				  ) call_details_record ON call_details_record.account_id = acc.id
	GROUP BY bill_base.bill_id, bill_base.bill_row, bill_base.bill_pays,
		ven.vendor_name, ven.call_center_phone, ven.address_string, ven.jur_info_string, ven.jur_info_rs, ven.jur_info_ks,
		ven.jur_info_inn, ven.jur_info_bank_name, ven.jur_info_bic,
		bill_base.params;

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Какой использовать индекс за столбцов с низким кардиналити? / 17 сообщений из 17, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 422ms
Закрыть
start [/forum/topic.php?fid=53&mobile=1&tid=1995739]:
 0ms
get settings:
 9ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 45ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 43ms
get tp. blocked users:
 1ms
others: 293ms
total:  422ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]