Доброго времени суток. Просьба подсказать по такому вот вопросу:

Есть запрос:
SELECT
card.id,
card.number,
card.company_id,
card.status,
card.date_create,
loyalty.name AS loyalty_program,
customer.digital_id,
customer_data.middle_name,
account.bonus_expected,
SUM(bonus.bonus_active - bonus.bonus_used) AS bonus_active,
SUM(bonus.bonus_express - bonus.bonus_express_used) AS bonus_express
FROM "card" "card"
LEFT JOIN customer ON customer.id = card.customer_id
LEFT JOIN customer_data ON customer_data.digital_id = customer.digital_id
LEFT JOIN account ON account.id = card.account_id
LEFT JOIN loyalty ON loyalty.id = card.loyalty_id
LEFT JOIN bonus AS bonus ON bonus.account_id = account.id
WHERE (((card.company_id = 2)
AND (customer.type IN (1, 2, 4)))
AND (bonus.bonus_active <> bonus.bonus_used OR bonus.bonus_express <> bonus.bonus_express_used))
AND (customer.digital_id > 0)
GROUP BY card.id, loyalty.name, customer.digital_id, customer_data.middle_name, account.bonus_expected
HAVING SUM(bonus.bonus_active - bonus.bonus_used) >= 400000
ORDER BY customer.digital_id ASC LIMIT 1000;

Таблица "бонус", поля которой участвуют в суммировании весит 70гб и порядка 241 076 377 строк.
я не сталкивался с индексацией таких запросов, поэтому даже разбор плана запроса особо не помог. В эксплейне этот кусок выглядит так:
Seq Scan on public.bonus (cost=0.00..7668822.14 rows=239923545 width=24) (actual time=1.799..123866.437 rows=28919998 loops=1)
Output: bonus.bonus_active, bonus.bonus_used, bonus.bonus_express, bonus.bonus_express_used, bonus.account_id
Filter: ((bonus.bonus_active <> bonus.bonus_used) OR (bonus.bonus_express <> bonus.bonus_express_used))
Rows Removed by Filter: 211746822
Buffers: shared hit=7499 read=4062380 dirtied=141 written=110

Можно ли тут какой то индекс создать для улучшения быстродействия?

Guzya,

Limit (cost=20615692.28..20615734.78 rows=1000 width=104) (actual time=372754.583..377002.759 rows=2 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, loyalty.name, customer.digital_id, customer_data.middle_name, account.bonus_expected, (sum((bonus.bonus_active - bonus.bonus_used))), (sum((bonus.bonus_express - bonus.bonus_express_used)))
Buffers: shared hit=9529 read=5580735 dirtied=657 written=10630, temp read=827382 written=827036
-> GroupAggregate (cost=20615692.28..21300332.47 rows=16109181 width=104) (actual time=372754.580..377002.753 rows=2 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, loyalty.name, customer.digital_id, customer_data.middle_name, account.bonus_expected, sum((bonus.bonus_active - bonus.bonus_used)), sum((bonus.bonus_express - bonus.bonus_express_used))
Group Key: customer.digital_id, card.id, loyalty.name, customer_data.middle_name, account.bonus_expected
Filter: (sum((bonus.bonus_active - bonus.bonus_used)) >= 400000)
Rows Removed by Filter: 1172747
Buffers: shared hit=9529 read=5580735 dirtied=657 written=10630, temp read=827382 written=827036
-> Sort (cost=20615692.28..20655965.23 rows=16109181 width=104) (actual time=372539.357..375653.756 rows=3164545 loops=1)
Output: card.id, loyalty.name, customer.digital_id, customer_data.middle_name, account.bonus_expected, card.number, card.company_id, card.status, card.date_create, bonus.bonus_active, bonus.bonus_used, bonus.bonus_express, bonus.bonus_express_used
Sort Key: customer.digital_id, card.id, loyalty.name, customer_data.middle_name, account.bonus_expected
Sort Method: external merge Disk: 433264kB
Buffers: shared hit=9529 read=5580735 dirtied=657 written=10630, temp read=827382 written=827036
-> Hash Join (cost=5624189.06..17806341.18 rows=16109181 width=104) (actual time=228688.627..367893.878 rows=3164545 loops=1)
Output: card.id, loyalty.name, customer.digital_id, customer_data.middle_name, account.bonus_expected, card.number, card.company_id, card.status, card.date_create, bonus.bonus_active, bonus.bonus_used, bonus.bonus_express, bonus.bonus_express_used
Hash Cond: (bonus.account_id = account.id)
Buffers: shared hit=9529 read=5580735 dirtied=657 written=10630, temp read=773193 written=772847
-> Seq Scan on public.bonus (cost=0.00..7668822.14 rows=239923545 width=24) (actual time=1.799..123866.437 rows=28919998 loops=1)
Output: bonus.bonus_active, bonus.bonus_used, bonus.bonus_express, bonus.bonus_express_used, bonus.account_id
Filter: ((bonus.bonus_active <> bonus.bonus_used) OR (bonus.bonus_express <> bonus.bonus_express_used))
Rows Removed by Filter: 211746822
Buffers: shared hit=7499 read=4062380 dirtied=141 written=110
-> Hash (cost=5550191.59..5550191.59 rows=2630998 width=104) (actual time=228593.402..228593.402 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, customer.digital_id, customer_data.middle_name, account.bonus_expected, account.id, loyalty.name
Buckets: 262144 (originally 262144) Batches: 32 (originally 16) Memory Usage: 32671kB
Buffers: shared hit=2030 read=1518355 dirtied=516 written=10520, temp read=567834 written=620598
-> Hash Left Join (cost=4063405.01..5550191.59 rows=2630998 width=104) (actual time=199132.950..227278.753 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, customer.digital_id, customer_data.middle_name, account.bonus_expected, account.id, loyalty.name
Hash Cond: (card.loyalty_id = loyalty.id)
Buffers: shared hit=2030 read=1518355 dirtied=516 written=10520, temp read=567834 written=567520
-> Hash Join (cost=4063400.00..5514010.36 rows=2630998 width=72) (actual time=199132.852..226541.162 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, card.loyalty_id, customer.digital_id, customer_data.middle_name, account.bonus_expected, account.id
Hash Cond: (account.id = card.account_id)
Buffers: shared hit=2027 read=1518355 dirtied=516 written=10520, temp read=567834 written=567520
-> Seq Scan on public.account (cost=0.00..768463.08 rows=39185008 width=12) (actual time=1.484..10087.957 rows=39171186 loops=1)
Output: account.bonus_expected, account.id
Buffers: shared hit=610 read=376003 dirtied=499 written=465
-> Hash (cost=4002249.53..4002249.53 rows=2630998 width=60) (actual time=199125.384..199125.384 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, card.loyalty_id, customer.digital_id, customer_data.middle_name
Buckets: 524288 Batches: 16 Memory Usage: 23989kB
Buffers: shared hit=1417 read=1142352 dirtied=17 written=10055, temp read=373357 written=406115
-> Hash Right Join (cost=2663884.20..4002249.53 rows=2630998 width=60) (actual time=167821.012..197972.025 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, card.loyalty_id, customer.digital_id, customer_data.middle_name
Hash Cond: (customer_data.digital_id = customer.digital_id)
Buffers: shared hit=1417 read=1142352 dirtied=17 written=10055, temp read=373357 written=373073
-> Seq Scan on public.customer_data (cost=0.00..645393.68 rows=35703368 width=18) (actual time=0.625..10936.005 rows=36475634 loops=1)
Output: customer_data.middle_name, customer_data.digital_id
Buffers: shared hit=253 read=288107 dirtied=11 written=11
-> Hash (cost=2605302.72..2605302.72 rows=2630998 width=50) (actual time=167818.364..167818.364 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, card.loyalty_id, customer.digital_id
Buckets: 524288 Batches: 16 Memory Usage: 22279kB
Buffers: shared hit=1164 read=854245 dirtied=6 written=10044, temp read=171305 written=200889
-> Hash Join (cost=1581773.33..2605302.72 rows=2630998 width=50) (actual time=20969.275..166725.445 rows=3104718 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.account_id, card.loyalty_id, customer.digital_id
Hash Cond: (card.customer_id = customer.id)
Buffers: shared hit=1164 read=854245 dirtied=6 written=10044, temp read=171305 written=171051
-> Bitmap Heap Scan on public.card (cost=62533.62..720036.16 rows=2924523 width=46) (actual time=1997.808..134248.025 rows=3104719 loops=1)
Output: card.id, card.number, card.company_id, card.status, card.date_create, card.customer_id, card.account_id, card.loyalty_id
Recheck Cond: (card.company_id = 2)
Rows Removed by Index Recheck: 10293275
Heap Blocks: exact=276124 lossy=212352
Buffers: shared hit=518 read=499757 dirtied=3 written=10037
-> Bitmap Index Scan on idx_card_company_id (cost=0.00..61802.49 rows=2924523 width=0) (actual time=1898.094..1898.094 rows=3151069 loops=1)
Index Cond: (card.company_id = 2)
Buffers: shared hit=4 read=11795 written=262
-> Hash (cost=948354.74..948354.74 rows=32841917 width=16) (actual time=18964.791..18964.791 rows=32988764 loops=1)
Output: customer.digital_id, customer.id
Buckets: 1048576 Batches: 128 Memory Usage: 20252kB
Buffers: shared hit=646 read=354488 dirtied=3 written=7, temp written=143783
-> Seq Scan on public.customer (cost=0.00..948354.74 rows=32841917 width=16) (actual time=0.816..13010.734 rows=32988764 loops=1)
Output: customer.digital_id, customer.id
Filter: ((customer.digital_id > 0) AND (customer.type = ANY ('{1,2,4}'::integer[])))
Rows Removed by Filter: 3690062
Buffers: shared hit=646 read=354488 dirtied=3 written=7
-> Hash (cost=3.89..3.89 rows=89 width=40) (actual time=0.051..0.051 rows=89 loops=1)
Output: loyalty.name, loyalty.id
Buckets: 1024 Batches: 1 Memory Usage: 15kB
Buffers: shared hit=3
-> Seq Scan on public.loyalty (cost=0.00..3.89 rows=89 width=40) (actual time=0.008..0.036 rows=89 loops=1)
Output: loyalty.name, loyalty.id
Buffers: shared hit=3
Planning time: 1.722 ms
Execution time: 377040.703 ms

Есть мысли создать либо составных индекса для по условию ((bonus.bonus_active <> bonus.bonus_used) OR (bonus.bonus_express <> bonus.bonus_express_used)) по полям участвующим в суммировании..

О-О-О,

да если б я мог ещё) это запрос от приклада, который мне скинули с заданием - сделай чтоб не тормозило. А тут с моей стороны единственное что в голову пришло
1) усечь таблицу удалив "исторические" данные (но их оказалось 1/5 от общего размера таблицы)
2) требовать улучшения ВМ и тюнить конфиг, увеличив основные параметры
3) докинуть индексы по полям bonus.bonus_active,bonus.bonus_used,bonus.bonus_express,bonus.bonus_express_used т.к. индексов по этим полям вообще нет...
А переписывать приклад у меня прав нет, могу только сказать чтобы привлекали разрабов и чтоб они как то решали этот вопрос..

Guzya,

Хм, а индекс по полю account_id там есть:
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+------------------------+------------+-----------------------------------------------------------------------------------------------------------------
public | bonus | bonus__pkey | | CREATE UNIQUE INDEX bonus__pkey ON public.bonus USING btree (id)
public | bonus | idx_bonus_action_id | | CREATE INDEX idx_bonus_action_id ON public.bonus USING btree (action_id) WHERE (action_id IS NOT NULL)
public | bonus | idx_bonus_sale_item_id | | CREATE INDEX idx_bonus_sale_item_id ON public.bonus USING btree (sale_item_id) WHERE (sale_item_id IS NOT NULL)
public | bonus | date_expire_idx | | CREATE INDEX date_expire_idx ON public.bonus USING btree (date_expire)
public | bonus | operation_idx | | CREATE INDEX operation_idx ON public.bonus USING btree (operation_id)
public | bonus | account_idx | | CREATE INDEX account_idx ON public.bonus USING btree (account_id)
public | bonus | idx_bonus_type | | CREATE INDEX idx_bonus_type ON public.bonus USING btree (type) WHERE (type <> ALL (ARRAY[1, 2, 4, 5]))
public | bonus | bonus_bonus_expected | | CREATE INDEX bonus_bonus_expected ON public.bonus USING btree (bonus_expected) WHERE (bonus_expected > 0)

по остальным таблицам:
card
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-----------------------------+------------+-----------------------------------------------------------------------------------------------------------------------------
public | card | idx_card_company_id_2 | | CREATE INDEX idx_card_company_id_2 ON public.card USING btree (company_id) WHERE (company_id = 2)
public | card | idx_card_account_id | | CREATE UNIQUE INDEX idx_card_account_id ON public.card USING btree (account_id)
public | card | idx_card_company_id | | CREATE INDEX idx_card_company_id ON public.card USING btree (company_id)
public | card | idx_card_customer_id | | CREATE INDEX idx_card_customer_id ON public.card USING btree (customer_id)
public | card | idxu_card_number_company_id | | CREATE UNIQUE INDEX idxu_card_number_company_id ON public.card USING btree (number, company_id)
public | card | idx_card_shop_id | | CREATE INDEX idx_card_shop_id ON public.card USING btree (shop_id)
public | card | idx_card_phone | | CREATE INDEX idx_card_phone ON public.card USING btree (phone) WHERE ((phone)::text > ''::text)
public | card | idx_card_email_lower | | CREATE INDEX idx_card_email_lower ON public.card USING btree (lower((email)::text)) WHERE (lower((email)::text) > ''::text)
public | card | idx_card_date_create | | CREATE INDEX idx_card_date_create ON public.card USING btree (date_create)
public | card | idx_card_date_modify | | CREATE INDEX idx_card_date_modify ON public.card USING btree (date_modify) WHERE (date_modify > 0)
public | card | idx_card_email | | CREATE INDEX idx_card_email ON public.card USING btree (email) WHERE ((email)::text > ''::text)
public | card | idx_card_source | | CREATE INDEX idx_card_source ON public.card USING btree (source) WHERE (source > 1)
public | card | idx_card_is_virtual | | CREATE INDEX idx_card_is_virtual ON public.card USING btree (is_virtual_card) WHERE (is_virtual_card IS TRUE)
public | card | idx_card_status | | CREATE INDEX idx_card_status ON public.card USING btree (status) WHERE (status <> 1)
public | card | company_id_status_idx | | CREATE INDEX company_id_status_idx ON public.card USING btree (company_id, status) WHERE (status = 2)
public | card | card_pk | | CREATE UNIQUE INDEX card_pk ON public.card USING btree (id)

customer
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-------------------------+------------+----------------------------------------------------------------------------------
public | customer | customer_digital_id_idx | | CREATE INDEX customer_digital_id_idx ON public.customer USING btree (digital_id)
public | customer | type_idx | | CREATE INDEX type_idx ON public.customer USING btree (type)
public | customer | customer_uuid_idx | | CREATE UNIQUE INDEX customer_uuid_idx ON public.customer USING btree (uuid)
public | customer | customer_pk | | CREATE UNIQUE INDEX customer_pk ON public.customer USING btree (id)

customer_data
schemaname | tablename | indexname | tablespace | indexdef
------------+---------------+----------------------------+------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public | customer_data | customer_data_birthday_idx | | CREATE INDEX customer_data_birthday_idx ON public.customer_data USING btree (digital_id, birthday)
public | customer_data | customer_data_idx | | CREATE INDEX customer_data_idx ON public.customer_data USING btree (digital_id)
public | customer_data | idx_customer_data_birthday | | CREATE INDEX idx_customer_data_birthday ON public.customer_data USING btree (((((date_part('month'::text, birthday))::integer << 8) + (date_part('day'::text, birthday))::integer))) WHERE (birthday IS NOT NULL)

account
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+-------------------------+------------+----------------------------------------------------------------------------------
public | account | idx_account_date_modify | | CREATE INDEX idx_account_date_modify ON public.account USING btree (date_modify)
public | account | account_pkey | | CREATE UNIQUE INDEX account_pkey ON public.account USING btree (id)

loyalty
schemaname | tablename | indexname | tablespace | indexdef
------------+-----------+--------------+------------+---------------------------------------------------------------------
public | loyalty | loyalty_pkey | | CREATE UNIQUE INDEX loyalty_pkey ON public.loyalty USING btree (id)

О-О-О,
Спасибо, попробую начать с параметров конфига, а там как пойдет.
Тут ещё версия СУБД, 9.5, это скорей всего тоже свой отпечаток накладывает. То же распараллеливание запросов начало работать с 9.6 вроде, так что все 32 ядра сейчас никак не помогают для ускорения...