Hey all, i've run into a performance problem with one of my queries that I am really not sure what is causing it.
Setup info:
Postgres version 9.4.4 on Debian 7. Server is virtual, with a single core and 512 ram available and ssd storage.
Changes to postgresql.conf:
maintenance_work_mem = 30MB
checkpoint_completion_target = 0.7
effective_cache_size = 352MB
work_mem = 24MB
wal_buffers = 4MB
checkpoint_segments = 8
shared_buffers = 120MB
random_page_cost = 1.1
The problem:
I have a view which sums up detail records to give totals at a header level, and performance is great when I select from it by limiting to a single record, but limiting it to multiple records seems to cause it to choose a bad plan.
Example 1:
SELECT *
FROM claim_totals
WHERE claim_id IN ('e8a38718-7997-4304-bbfa-138deb84aa82')
(2 ms)
Example 2:
SELECT *
FROM claim_totals
WHERE claim_id IN ('324d2af8-46b3-45ad-b56a-0a49d0345653', 'e8a38718-7997-4304-bbfa-138deb84aa82')
(5460 ms)
The view definition is:
SELECT claim.claim_id,
COALESCE(lumpsum.lumpsum_count, 0::bigint)::integer AS lumpsum_count,
COALESCE(lumpsum.requested_amount, 0::numeric) AS lumpsum_requested_total,
COALESCE(lumpsum.allowed_amount, 0::numeric) AS lumpsum_allowed_total,
COALESCE(claim_product.product_count, 0::bigint)::integer + COALESCE(claim_adhoc_product.adhoc_product_count, 0::bigint)::integer AS product_count,
COALESCE(claim_product.requested_amount, 0::numeric) + COALESCE(claim_adhoc_product.requested_amount, 0::numeric) AS product_requested_amount,
COALESCE(claim_product.allowed_amount, 0::numeric) + COALESCE(claim_adhoc_product.allowed_amount, 0::numeric) AS product_allowed_amount,
COALESCE(claim_product.requested_amount, 0::numeric) + COALESCE(claim_adhoc_product.requested_amount, 0::numeric) + COALESCE(lumpsum.requested_amount, 0::numeric) AS requested_total,
COALESCE(claim_product.allowed_amount, 0::numeric) + COALESCE(claim_adhoc_product.allowed_amount, 0::numeric) + COALESCE(lumpsum.allowed_amount, 0::numeric) AS allowed_total
FROM claim
LEFT JOIN ( SELECT claim_lumpsum.claim_id,
count(claim_lumpsum.claim_lumpsum_id) AS lumpsum_count,
sum(claim_lumpsum.requested_amount) AS requested_amount,
sum(claim_lumpsum.allowed_amount) AS allowed_amount
FROM claim_lumpsum
GROUP BY claim_lumpsum.claim_id) lumpsum ON lumpsum.claim_id = claim.claim_id
LEFT JOIN ( SELECT claim_product_1.claim_id,
count(claim_product_1.claim_product_id) AS product_count,
sum(claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate) AS requested_amount,
sum(claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate) AS allowed_amount
FROM claim_product claim_product_1
GROUP BY claim_product_1.claim_id) claim_product ON claim_product.claim_id = claim.claim_id
LEFT JOIN ( SELECT claim_adhoc_product_1.claim_id,
count(claim_adhoc_product_1.claim_adhoc_product_id) AS adhoc_product_count,
sum(claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate) AS requested_amount,
sum(claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate) AS allowed_amount
FROM claim_adhoc_product claim_adhoc_product_1
GROUP BY claim_adhoc_product_1.claim_id) claim_adhoc_product ON claim_adhoc_product.claim_id = claim.claim_id;
Here are the respective explain / analyze for the two queries above:
Example 1:
Nested Loop Left Join (cost=0.97..149.46 rows=2 width=232) (actual time=0.285..0.289 rows=1 loops=1)
Output: claim.claim_id, (COALESCE((count(claim_lumpsum.claim_lumpsum_id)), 0::bigint))::integer, COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric), COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric), ((COALESCE((count(claim_product_1.claim_product_id)), 0::bigint))::integer + (COALESCE((count(claim_adhoc_product_1.claim_adhoc_product_id)), 0::bigint))::integer), (COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)), (COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric))
Join Filter: (claim_lumpsum.claim_id = claim.claim_id)
-> Nested Loop Left Join (cost=0.97..135.31 rows=1 width=160) (actual time=0.260..0.264 rows=1 loops=1)
Output: claim.claim_id, (count(claim_product_1.claim_product_id)), (sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), (sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), (count(claim_adhoc_product_1.claim_adhoc_product_id)), (sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), (sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate)))
Join Filter: (claim_adhoc_product_1.claim_id = claim.claim_id)
-> Nested Loop Left Join (cost=0.97..122.14 rows=1 width=88) (actual time=0.254..0.256 rows=1 loops=1)
Output: claim.claim_id, (count(claim_product_1.claim_product_id)), (sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), (sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate)))
Join Filter: (claim_product_1.claim_id = claim.claim_id)
-> Index Only Scan using claim_pkey on client_pinnacle.claim (cost=0.42..1.54 rows=1 width=16) (actual time=0.078..0.079 rows=1 loops=1)
Output: claim.claim_id
Index Cond: (claim.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
Heap Fetches: 0
-> GroupAggregate (cost=0.55..120.58 rows=1 width=54) (actual time=0.163..0.163 rows=1 loops=1)
Output: claim_product_1.claim_id, count(claim_product_1.claim_product_id), sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate)), sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))
Group Key: claim_product_1.claim_id
-> Index Scan using claim_product_claim_id_product_id_distributor_company_id_lo_key on client_pinnacle.claim_product claim_product_1 (cost=0.55..118.99 rows=105 width=54) (actual time=0.071..0.091 rows=12 loops=1)
Output: claim_product_1.claim_id, claim_product_1.claim_product_id, claim_product_1.rebate_requested_quantity, claim_product_1.rebate_requested_rate, claim_product_1.rebate_allowed_quantity, claim_product_1.rebate_allowed_rate
Index Cond: (claim_product_1.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
-> GroupAggregate (cost=0.00..13.15 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=1)
Output: claim_adhoc_product_1.claim_id, count(claim_adhoc_product_1.claim_adhoc_product_id), sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate)), sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))
Group Key: claim_adhoc_product_1.claim_id
-> Seq Scan on client_pinnacle.claim_adhoc_product claim_adhoc_product_1 (cost=0.00..13.12 rows=1 width=160) (actual time=0.001..0.001 rows=0 loops=1)
Output: claim_adhoc_product_1.claim_adhoc_product_id, claim_adhoc_product_1.claim_id, claim_adhoc_product_1.product_name, claim_adhoc_product_1.product_number, claim_adhoc_product_1.uom_type_description, claim_adhoc_product_1.rebate_requested_quantity, claim_adhoc_product_1.rebate_requested_rate, claim_adhoc_product_1.rebate_allowed_quantity, claim_adhoc_product_1.rebate_allowed_rate, claim_adhoc_product_1.claimant_contract_name, claim_adhoc_product_1.resolve_date
Filter: (claim_adhoc_product_1.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
-> GroupAggregate (cost=0.00..14.05 rows=2 width=96) (actual time=0.001..0.001 rows=0 loops=1)
Output: claim_lumpsum.claim_id, count(claim_lumpsum.claim_lumpsum_id), sum(claim_lumpsum.requested_amount), sum(claim_lumpsum.allowed_amount)
Group Key: claim_lumpsum.claim_id
-> Seq Scan on client_pinnacle.claim_lumpsum (cost=0.00..14.00 rows=2 width=96) (actual time=0.000..0.000 rows=0 loops=1)
Output: claim_lumpsum.claim_lumpsum_id, claim_lumpsum.claim_id, claim_lumpsum.lumpsum_id, claim_lumpsum.requested_amount, claim_lumpsum.allowed_amount, claim_lumpsum.event_date_range, claim_lumpsum.contract_lumpsum_id, claim_lumpsum.claimant_contract_name, claim_lumpsum.hint_contract_lumpsum_description
Filter: (claim_lumpsum.claim_id = 'e8a38718-7997-4304-bbfa-138deb84aa82'::uuid)
Planning time: 6.336 ms
Execution time: 0.753 ms
Example 2:
Hash Right Join (cost=81278.79..81674.85 rows=2 width=232) (actual time=5195.972..5458.916 rows=2 loops=1)
Output: claim.claim_id, (COALESCE((count(claim_lumpsum.claim_lumpsum_id)), 0::bigint))::integer, COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric), COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric), ((COALESCE((count(claim_product_1.claim_product_id)), 0::bigint))::integer + (COALESCE((count(claim_adhoc_product_1.claim_adhoc_product_id)), 0::bigint))::integer), (COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)), (COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.requested_amount)), 0::numeric)), ((COALESCE((sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))), 0::numeric) + COALESCE((sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))), 0::numeric)) + COALESCE((sum(claim_lumpsum.allowed_amount)), 0::numeric))
Hash Cond: (claim_product_1.claim_id = claim.claim_id)
-> HashAggregate (cost=81231.48..81438.09 rows=13774 width=54) (actual time=5182.546..5405.990 rows=95763 loops=1)
Output: claim_product_1.claim_id, count(claim_product_1.claim_product_id), sum((claim_product_1.rebate_requested_quantity * claim_product_1.rebate_requested_rate)), sum((claim_product_1.rebate_allowed_quantity * claim_product_1.rebate_allowed_rate))
Group Key: claim_product_1.claim_id
-> Seq Scan on client_pinnacle.claim_product claim_product_1 (cost=0.00..55253.59 rows=1731859 width=54) (actual time=0.020..1684.826 rows=1731733 loops=1)
Output: claim_product_1.claim_id, claim_product_1.claim_product_id, claim_product_1.rebate_requested_quantity, claim_product_1.rebate_requested_rate, claim_product_1.rebate_allowed_quantity, claim_product_1.rebate_allowed_rate
-> Hash (cost=47.29..47.29 rows=2 width=160) (actual time=0.110..0.110 rows=2 loops=1)
Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount)), (count(claim_adhoc_product_1.claim_adhoc_product_id)), (sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), (sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate)))
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Hash Right Join (cost=41.53..47.29 rows=2 width=160) (actual time=0.105..0.108 rows=2 loops=1)
Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount)), (count(claim_adhoc_product_1.claim_adhoc_product_id)), (sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate))), (sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate)))
Hash Cond: (claim_adhoc_product_1.claim_id = claim.claim_id)
-> HashAggregate (cost=16.25..19.25 rows=200 width=160) (actual time=0.001..0.001 rows=0 loops=1)
Output: claim_adhoc_product_1.claim_id, count(claim_adhoc_product_1.claim_adhoc_product_id), sum((claim_adhoc_product_1.rebate_requested_quantity * claim_adhoc_product_1.rebate_requested_rate)), sum((claim_adhoc_product_1.rebate_allowed_quantity * claim_adhoc_product_1.rebate_allowed_rate))
Group Key: claim_adhoc_product_1.claim_id
-> Seq Scan on client_pinnacle.claim_adhoc_product claim_adhoc_product_1 (cost=0.00..12.50 rows=250 width=160) (actual time=0.001..0.001 rows=0 loops=1)
Output: claim_adhoc_product_1.claim_adhoc_product_id, claim_adhoc_product_1.claim_id, claim_adhoc_product_1.product_name, claim_adhoc_product_1.product_number, claim_adhoc_product_1.uom_type_description, claim_adhoc_product_1.rebate_requested_quantity, claim_adhoc_product_1.rebate_requested_rate, claim_adhoc_product_1.rebate_allowed_quantity, claim_adhoc_product_1.rebate_allowed_rate, claim_adhoc_product_1.claimant_contract_name, claim_adhoc_product_1.resolve_date
-> Hash (cost=25.25..25.25 rows=2 width=88) (actual time=0.093..0.093 rows=2 loops=1)
Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount))
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Hash Right Join (cost=19.49..25.25 rows=2 width=88) (actual time=0.088..0.092 rows=2 loops=1)
Output: claim.claim_id, (count(claim_lumpsum.claim_lumpsum_id)), (sum(claim_lumpsum.requested_amount)), (sum(claim_lumpsum.allowed_amount))
Hash Cond: (claim_lumpsum.claim_id = claim.claim_id)
-> HashAggregate (cost=16.40..19.40 rows=200 width=96) (actual time=0.003..0.003 rows=0 loops=1)
Output: claim_lumpsum.claim_id, count(claim_lumpsum.claim_lumpsum_id), sum(claim_lumpsum.requested_amount), sum(claim_lumpsum.allowed_amount)
Group Key: claim_lumpsum.claim_id
-> Seq Scan on client_pinnacle.claim_lumpsum (cost=0.00..13.20 rows=320 width=96) (actual time=0.001..0.001 rows=0 loops=1)
Output: claim_lumpsum.claim_lumpsum_id, claim_lumpsum.claim_id, claim_lumpsum.lumpsum_id, claim_lumpsum.requested_amount, claim_lumpsum.allowed_amount, claim_lumpsum.event_date_range, claim_lumpsum.contract_lumpsum_id, claim_lumpsum.claimant_contract_name, claim_lumpsum.hint_contract_lumpsum_description
-> Hash (cost=3.07..3.07 rows=2 width=16) (actual time=0.073..0.073 rows=2 loops=1)
Output: claim.claim_id
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> Index Only Scan using claim_pkey on client_pinnacle.claim (cost=0.42..3.07 rows=2 width=16) (actual time=0.048..0.070 rows=2 loops=1)
Output: claim.claim_id
Index Cond: (claim.claim_id = ANY ('{324d2af8-46b3-45ad-b56a-0a49d0345653,e8a38718-7997-4304-bbfa-138deb84aa82}'::uuid[]))
Heap Fetches: 0
Planning time: 1.020 ms
Execution time: 5459.461 ms
Please let me know if there is any more info I can provide to help figure out why it's choosing an undesirable plan with just a slight change in the the clause.
Thanks for any help you may be able to provide.
-Adam
