From: | Evgeniy Shishkin <itparanoia(at)gmail(dot)com> |
---|---|
To: | Adam Brusselback <adambrusselback(at)gmail(dot)com> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Slow update on column that is part of exclusion constraint |
Date: | 2016-04-13 18:54:45 |
Message-ID: | 5C918770-5BC7-43E2-90B1-9A90174370F6@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> On 13 Apr 2016, at 20:14, Adam Brusselback <adambrusselback(at)gmail(dot)com> wrote:
>
> Sorry, brain stopped working and I forgot to include the normal info.
>
> Postgres version: 9.5.1
> Hardware: 2 core, 4gb Digital Ocean virtual server
> OS: Debian
>
> explain analyze for an example update:
> 'Update on price_generated (cost=32.45..644.83 rows=1 width=157) (actual time=29329.614..29329.614 rows=0 loops=1)'
> ' -> Nested Loop (cost=32.45..644.83 rows=1 width=157) (actual time=29329.608..29329.608 rows=0 loops=1)'
> ' -> HashAggregate (cost=32.04..34.35 rows=231 width=52) (actual time=1.137..2.090 rows=231 loops=1)'
> ' Group Key: pti.product_id, pti.company_id, pti.date_range'
> ' -> Seq Scan on _prices_to_insert pti (cost=0.00..30.31 rows=231 width=52) (actual time=0.060..0.678 rows=231 loops=1)'
> ' -> Index Scan using price_generated_company_product_date_active_excl on price_generated (cost=0.41..2.63 rows=1 width=151) (actual time=126.949..126.949 rows=0 loops=231)'
> ' Index Cond: (date_range = pti.date_range)'
> ' Filter: ((upper(active_range) IS NULL) AND (pti.product_id = product_id) AND (pti.company_id = company_id))'
> ' Rows Removed by Filter: 29460'
> 'Planning time: 3.134 ms'
> 'Execution time: 29406.717 ms'
Well, you see execution time of 30 seconds because there are 231 index lookups,
each taking 126 ms.
And that lookup is slow because of
Filter: ((upper(active_range) IS NULL) AND (pti.product_id = product_id) AND (pti.company_id = company_id))'
Can you provide self-containing example of update?
I don't see there (upper(active_range) IS NULL condition is coming from.
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Brusselback | 2016-04-14 04:17:42 | Re: Slow update on column that is part of exclusion constraint |
Previous Message | Adam Brusselback | 2016-04-13 17:14:29 | Re: Slow update on column that is part of exclusion constraint |