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
