Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1aqON9-0008Ib-Tm for pgsql-performance@arkaria.postgresql.org; Wed, 13 Apr 2016 17:14:36 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1aqON9-0002QY-GO for pgsql-performance@arkaria.postgresql.org; Wed, 13 Apr 2016 17:14:35 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1aqON8-0002PN-Iz for pgsql-performance@postgresql.org; Wed, 13 Apr 2016 17:14:34 +0000 Received: from mail-lf0-x233.google.com ([2a00:1450:4010:c07::233]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1aqON5-0000Ph-EA for pgsql-performance@postgresql.org; Wed, 13 Apr 2016 17:14:33 +0000 Received: by mail-lf0-x233.google.com with SMTP id j11so78734319lfb.1 for ; Wed, 13 Apr 2016 10:14:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to; bh=e20PQU6uEgATS7Ew2lEgPp9IJ0SolwAcqjES8WrKVU8=; b=VRkk1NMRfD/WpVCmFhwXXmVQVjKa0z1O9lC+THplYTj+9pfCgQfHxdJsphJmtUf9J/ DXR7wa5AfOc/C8604/2WTyKNCDaZ0VFfJnkMNvbJE1bSu7ltUW6DRTZ3kN8v7JW7DI04 j1iGy8XjSXcVAQoLfZ1tYKFWc4+g1urO7Nv2kQ4NDS+N31H/ddo2lcn+4f8O9JoJAEJ+ 0AGpaP4PaLEkR5+MKVnkqoModFnooL6Ri580JWDKBeiJdI51VygK5dJM7M27GsqeXlfW t+FFmkrnsAdfzZolpKySTRO3au1QRZNvkeqTeeGAt0q+2Kn0XkcKw77t4za6u7zp468e OU/A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to; bh=e20PQU6uEgATS7Ew2lEgPp9IJ0SolwAcqjES8WrKVU8=; b=DjEbo5zcqLiOuaYujbyK6v1KduGDr8yyHoJ8bpi9OJgd5C/u3H0bXT5TOqwQQDjnJY +2LDcnIQcEn8GCU+UvYNy3BODgahBwi+EDg3WxR34wJszxSwgG+j49VKgCNaFDes0utZ dfdkbuGAQLpwltJ5hZjDsTLrE7tuMs8Xrij5FXzaWJPMXiii5h63rIWWSw344nnHrZJW 2kIs4fI+9YMZ+Y7ou6hr3QXgjWPdyy6MY6/4NWgXUH5xgXjEagPshlxTIlHD+f9pQK1+ U3qeD18QfcnXu5GOA8Vxi48AZW+6/sgRK/rTIgEKT7CMXicg4+9iPQAr5wQifa182fRK /WDQ== X-Gm-Message-State: AOPr4FU/DxIeGTbfWAcPcOc10A7w9+u/XA3td74b0qgU22K3lP+1PcUr8RhuFWgWq4LQpCxVublS5fS9+Mni4g== MIME-Version: 1.0 X-Received: by 10.25.152.205 with SMTP id a196mr4681899lfe.85.1460567669671; Wed, 13 Apr 2016 10:14:29 -0700 (PDT) Received: by 10.25.207.74 with HTTP; Wed, 13 Apr 2016 10:14:29 -0700 (PDT) In-Reply-To: References: Date: Wed, 13 Apr 2016 13:14:29 -0400 Message-ID: Subject: Re: Slow update on column that is part of exclusion constraint From: Adam Brusselback To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=001a11401c961f1033053060e91c X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11401c961f1033053060e91c Content-Type: text/plain; charset=UTF-8 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' --001a11401c961f1033053060e91c Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Sorry, brain stopped working and I forgot to include the n= ormal info.

Postgres version: 9.5.1
Hardware: = 2 core, 4gb Digital Ocean virtual server
OS: Debian=C2=A0

explain analyze for an example update:
= 9;Update on price_generated =C2=A0(cost=3D32.45..644.83 rows=3D1 width=3D15= 7) (actual time=3D29329.614..29329.614 rows=3D0 loops=3D1)'
&= #39; =C2=A0-> =C2=A0Nested Loop =C2=A0(cost=3D32.45..644.83 rows=3D1 wid= th=3D157) (actual time=3D29329.608..29329.608 rows=3D0 loops=3D1)'
' =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0HashAggregate =C2=A0(cos= t=3D32.04..34.35 rows=3D231 width=3D52) (actual time=3D1.137..2.090 rows=3D= 231 loops=3D1)'
' =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Group Key: pti.product_id, pti.company_id, pti.date_range'
' =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0S= eq Scan on _prices_to_insert pti =C2=A0(cost=3D0.00..30.31 rows=3D231 width= =3D52) (actual time=3D0.060..0.678 rows=3D231 loops=3D1)'
= 9; =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Scan using price_generated_= company_product_date_active_excl on price_generated =C2=A0(cost=3D0.41..2.6= 3 rows=3D1 width=3D151) (actual time=3D126.949..126.949 rows=3D0 loops=3D23= 1)'
' =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Ind= ex Cond: (date_range =3D pti.date_range)'
' =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Filter: ((upper(active_range) IS NULL) A= ND (pti.product_id =3D product_id) AND (pti.company_id =3D company_id))'= ;
' =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Rows Remo= ved by Filter: 29460'
'Planning time: 3.134 ms'
=
'Execution time: 29406.717 ms'
--001a11401c961f1033053060e91c--