[PERFORM] Query order of magnitude slower with slightly different where clause

[Adam Brusselback]

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)) +
> 

Re: [PERFORM] Query order of magnitude slower with slightly different where clause

[David Rowley]

On 18 January 2016 at 10:41, Adam Brusselback 
wrote:

> 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 *
>>