Postgres Pro
Downloads
Home   >   mailing lists

Re: Slow update on column that is part of exclusion constraint - Mailing list pgsql-performance

From Adam Brusselback
Subject Re: Slow update on column that is part of exclusion constraint
Date
Msg-id CAMjNa7dkzOZeAPfdxXo3Tf604xx-F+iRyX_M+GbvMHws2DtsrA@mail.gmail.com
Whole thread Raw
In response to Re: Slow update on column that is part of exclusion constraint  (Evgeniy Shishkin <itparanoia@gmail.com>)
Responses Re: Slow update on column that is part of exclusion constraint  (Evgeniy Shishkin <itparanoia@gmail.com>)
List pgsql-performance
Tree view
So fair enough, it does seem to be related to the lookup rather than maintenance on the index. I was misguided in my initial assumption.  

Spent quite a bit of time trying to come up with a self contained test, and it seems like I can't make it choose the GiST index unless I remove the regular btree index in my test case, though the opposite is true for my table in production.  Not really sure what that means as far as what I need to do though. I've tried a vacuum full, analyze, rebuild index, drop and re-add the constraint... It still uses that GiST index for this query.

Hell, a sequential scan is a ton faster even.

On Wed, Apr 13, 2016 at 2:54 PM, Evgeniy Shishkin <itparanoia@gmail.com> wrote:

> On 13 Apr 2016, at 20:14, Adam Brusselback <adambrusselback@gmail.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.

Attachment

pgsql-performance by date:

Previous
From: Evgeniy Shishkin
Date:
Subject: Re: Slow update on column that is part of exclusion constraint
Next
From: Evgeniy Shishkin
Date:
Subject: Re: Slow update on column that is part of exclusion constraint