MIME-Version: 1.0
Date: Wed, 13 Apr 2016 13:03:47 -0400
Message-ID: 
 <CAMjNa7ck5JZxooWUsZ_ag71HA=GB_zKzpMOgaFO0Z5+YfEerOA@mail.gmail.com>
Subject: Slow update on column that is part of exclusion constraint
From: Adam Brusselback <adambrusselback@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a11347e8ed6c0aa053060c299
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a11347e8ed6c0aa053060c299
Content-Type: text/plain; charset=UTF-8

Hey all, been running into some performance issues with one of my tables,
and it seems to be centered around index maintenance.

I have a table to store aggregated prices that are derived from sale data
over a configurable period, and a function that runs periodically that
inserts new prices if necessary, or "inactivates" the old record and
inserts new ones.  We use that price in calculations, and store the price
we used for a specific calculation for audit purposes, so the old record
cannot just be updated or deleted. We need a new record every time.

So to do that we have an "active_range" column on the table, and that is
 used to set the periods that each specific price was in use for (important
for audit).

The issue seems to be the exclusion constraint we have on the table to
ensure data consistency... It is ridiculously slow to insert / update.  To
update 200 records, it takes 45 seconds. To update 1500 rows takes about
3.5 min. To build the constraint/index fresh on the table (1,392,085 rows)
it takes about 4 min.

Looking at the schema, is there just a clearly better way to do this? I
know uuid is not supported by GiST indexes yet, so casting to text isn't
great, but necessary (as far as I know...) at the moment.

The table definition looks like so:
CREATE TABLE price_generated
(
  price_generated_id uuid NOT NULL DEFAULT gen_random_uuid(),
  product_id uuid NOT NULL,
  company_id uuid NOT NULL,
  date_range daterange NOT NULL,
  average_price numeric NOT NULL,
  average_price_delivered numeric NOT NULL,
  low_price numeric NOT NULL,
  low_price_delivered numeric NOT NULL,
  high_price numeric NOT NULL,
  high_price_delivered numeric NOT NULL,
  uom_type_id uuid NOT NULL,
  active_range tstzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp
with time zone),
  CONSTRAINT price_generated_pkey PRIMARY KEY (price_generated_id),
  CONSTRAINT price_generated_company_id_fkey FOREIGN KEY (company_id)
      REFERENCES public.company (company_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT price_generated_product_id_fkey FOREIGN KEY (product_id)
      REFERENCES public.product (product_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT price_generated_company_product_date_active_excl EXCLUDE
  USING gist ((company_id::text) WITH =, (product_id::text) WITH =,
date_range WITH &&, active_range WITH &&)
);

CREATE INDEX idx_price_generated_prod_comp_date
  ON price_generated
  USING btree
  (product_id, company_id, date_range);

--001a11347e8ed6c0aa053060c299
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hey all, been running into some performance issues with on=
e of my tables, and it seems to be centered around index maintenance.<div><=
br></div><div>I have a table to store aggregated prices that are derived fr=
om sale data over a configurable period, and a function that runs periodica=
lly that inserts new prices if necessary, or &quot;inactivates&quot; the ol=
d record and inserts new ones.=C2=A0 We use that price in calculations, and=
 store the price we used for a specific calculation for audit purposes, so =
the old record cannot just be updated or deleted. We need a new record ever=
y time.</div><div><br></div><div>So to do that we have an &quot;active_rang=
e&quot; column on the table, and that is =C2=A0used to set the periods that=
 each specific price was in use for (important for audit).</div><div><br></=
div><div>The issue seems to be the exclusion constraint we have on the tabl=
e to ensure data consistency... It is ridiculously slow to insert / update.=
=C2=A0 To update 200 records, it takes 45 seconds. To update 1500 rows take=
s about 3.5 min. To build the constraint/index fresh on the table (1,392,08=
5 rows) it takes about 4 min.</div><div><br></div><div>Looking at the schem=
a, is there just a clearly better way to do this? I know uuid is not suppor=
ted by GiST indexes yet, so casting to text isn&#39;t great, but necessary =
(as far as I know...) at the moment.</div><div><br></div><div>The table def=
inition looks like so:</div><div><div>CREATE TABLE price_generated</div><di=
v>(</div><div>=C2=A0 price_generated_id uuid NOT NULL DEFAULT gen_random_uu=
id(),</div><div>=C2=A0 product_id uuid NOT NULL,</div><div>=C2=A0 company_i=
d uuid NOT NULL,</div><div>=C2=A0 date_range daterange NOT NULL,</div><div>=
=C2=A0 average_price numeric NOT NULL,</div><div>=C2=A0 average_price_deliv=
ered numeric NOT NULL,</div><div>=C2=A0 low_price numeric NOT NULL,</div><d=
iv>=C2=A0 low_price_delivered numeric NOT NULL,</div><div>=C2=A0 high_price=
 numeric NOT NULL,</div><div>=C2=A0 high_price_delivered numeric NOT NULL,<=
/div><div>=C2=A0 uom_type_id uuid NOT NULL,</div><div>=C2=A0 active_range t=
stzrange NOT NULL DEFAULT tstzrange(now(), NULL::timestamp with time zone),=
</div><div>=C2=A0 CONSTRAINT price_generated_pkey PRIMARY KEY (price_genera=
ted_id),</div><div>=C2=A0 CONSTRAINT price_generated_company_id_fkey FOREIG=
N KEY (company_id)</div><div>=C2=A0 =C2=A0 =C2=A0 REFERENCES public.company=
 (company_id) MATCH SIMPLE</div><div>=C2=A0 =C2=A0 =C2=A0 ON UPDATE NO ACTI=
ON ON DELETE NO ACTION,</div><div>=C2=A0 CONSTRAINT price_generated_product=
_id_fkey FOREIGN KEY (product_id)</div><div>=C2=A0 =C2=A0 =C2=A0 REFERENCES=
 public.product (product_id) MATCH SIMPLE</div><div>=C2=A0 =C2=A0 =C2=A0 ON=
 UPDATE NO ACTION ON DELETE NO ACTION,</div><div>=C2=A0 CONSTRAINT price_ge=
nerated_company_product_date_active_excl EXCLUDE=C2=A0</div><div>=C2=A0 USI=
NG gist ((company_id::text) WITH =3D, (product_id::text) WITH =3D, date_ran=
ge WITH &amp;&amp;, active_range WITH &amp;&amp;)</div><div>);</div></div><=
div><br></div><div><div>CREATE INDEX idx_price_generated_prod_comp_date</di=
v><div>=C2=A0 ON price_generated</div><div>=C2=A0 USING btree</div><div>=C2=
=A0 (product_id, company_id, date_range);</div></div></div>

--001a11347e8ed6c0aa053060c299--