Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 9.3 \(3124\))
Subject: Re: Slow update on column that is part of exclusion constraint
From: Evgeniy Shishkin <itparanoia@gmail.com>
In-Reply-To: 
 <CAMjNa7e6XeyAue0XEi=Pnc8rRp5qK6RvnTg69woTGTG6zw9rcQ@mail.gmail.com>
Date: Wed, 13 Apr 2016 21:54:45 +0300
Cc: pgsql-performance@postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <5C918770-5BC7-43E2-90B1-9A90174370F6@gmail.com>
References: 
 <CAMjNa7ck5JZxooWUsZ_ag71HA=GB_zKzpMOgaFO0Z5+YfEerOA@mail.gmail.com>
 <CAMjNa7e6XeyAue0XEi=Pnc8rRp5qK6RvnTg69woTGTG6zw9rcQ@mail.gmail.com>
To: Adam Brusselback <adambrusselback@gmail.com>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> On 13 Apr 2016, at 20:14, Adam Brusselback <adambrusselback@gmail.com> wr=
ote:
>=20
> Sorry, brain stopped working and I forgot to include the normal info.
>=20
> Postgres version: 9.5.1
> Hardware: 2 core, 4gb Digital Ocean virtual server
> OS: Debian=20
>=20
> explain analyze for an example update:
> 'Update on price_generated  (cost=3D32.45..644.83 rows=3D1 width=3D157) (=
actual time=3D29329.614..29329.614 rows=3D0 loops=3D1)'
> '  ->  Nested Loop  (cost=3D32.45..644.83 rows=3D1 width=3D157) (actual t=
ime=3D29329.608..29329.608 rows=3D0 loops=3D1)'
> '        ->  HashAggregate  (cost=3D32.04..34.35 rows=3D231 width=3D52) (=
actual time=3D1.137..2.090 rows=3D231 loops=3D1)'
> '              Group Key: pti.product_id, pti.company_id, pti.date_range'
> '              ->  Seq Scan on _prices_to_insert pti  (cost=3D0.00..30.31=
 rows=3D231 width=3D52) (actual time=3D0.060..0.678 rows=3D231 loops=3D1)'
> '        ->  Index Scan using price_generated_company_product_date_active=
_excl on price_generated  (cost=3D0.41..2.63 rows=3D1 width=3D151) (actual =
time=3D126.949..126.949 rows=3D0 loops=3D231)'
> '              Index Cond: (date_range =3D pti.date_range)'
> '              Filter: ((upper(active_range) IS NULL) AND (pti.product_id=
 =3D product_id) AND (pti.company_id =3D 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 look=
ups,
each taking 126 ms.

And that lookup is slow because of
Filter: ((upper(active_range) IS NULL) AND (pti.product_id =3D product_id) =
AND (pti.company_id =3D company_id))'

Can you provide self-containing example of update?
I don't see there (upper(active_range) IS NULL condition is coming from.

--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance