Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\))
Subject: Re: update from performance question
From: "Armand Pirvu (home)" <armand.pirvu@gmail.com>
In-Reply-To: 
 <A737B7A37273E048B164557ADEF4A58B53A27A53@ntex2010i.host.magwien.gv.at>
Date: Wed, 19 Apr 2017 18:11:06 -0500
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <CAB35198-1D1F-4FB0-A574-338B6DE574E9@gmail.com>
References: <38D1728F-B930-44B7-92EA-29225176E238@gmail.com>
 <A737B7A37273E048B164557ADEF4A58B53A27A53@ntex2010i.host.magwien.gv.at>
To: Albe Laurenz <laurenz.albe@wien.gv.at>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi Albe


Thank you for your reply

The query changed a bit


explain (analyze, buffers)
UPDATE
    csischema.tf_transaction_item_person TRANS
SET=20
    general_ledger_code =3D PURCH.general_ledger_code,
    general_ledger_code_desc =3D PURCH.general_ledger_code_desc,
    update_datetime =3D now()::timestamp(0)
FROM
    csischema.tf_purchases_person PURCH
WHERE
    PURCH.general_ledger_code IS NOT NULL AND
    TRANS.purchased_log_id =3D PURCH.purchased_log_id AND
    TRANS.general_ledger_code IS NULL
;

                                                             ^
select count(*) from csischema.tf_transaction_item_person where general_led=
ger_code is null;
  count=20=20
---------
 1393515

select count(*) from csischema.tf_transaction_item_person ;
  count=20=20
---------
 3408380

select count(*) from csischema.tf_purchases_person;
  count=20=20=20
----------
 20760731

select count(*) from csischema.tf_purchases_person where general_ledger_cod=
e IS NOT NULL;
  count=20=20
---------
 6909204


But the kicker is this

A select count to see how many records will be used for update gets me zero


select count(trans.purchased_log_id) from=20
    csischema.tf_transaction_item_person TRANS,
    csischema.tf_purchases_person PURCH
WHERE
    PURCH.general_ledger_code IS NOT NULL AND
    TRANS.purchased_log_id =3D PURCH.purchased_log_id AND
    TRANS.general_ledger_code IS NULL
;
 count=20
-------
     0
(1 row)

Considering this , I wonder if an index on csischema.tf_purchases_person (p=
urchased_log_id, general_ledger_code) and one on tf_transaction_item_person=
 (purchased_log_id, general_ledger_code) would not help ?

This is what bugs me.=20

I got the explain out


without indexes


                                                                          Q=
UERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------
 Update on tf_transaction_item_person trans  (cost=3D1164684.43..1572235.51=
 rows=3D507748 width=3D227) (actual time=3D230320.060..230320.060 rows=3D0 =
loops=3D1)
   Buffers: shared hit=3D120188 read=3D876478, temp read=3D93661 written=3D=
93631
   ->  Hash Join  (cost=3D1164684.43..1572235.51 rows=3D507748 width=3D227)=
 (actual time=3D230320.054..230320.054 rows=3D0 loops=3D1)
         Hash Cond: ((trans.purchased_log_id)::text =3D (purch.purchased_lo=
g_id)::text)
         Buffers: shared hit=3D120188 read=3D876478, temp read=3D93661 writ=
ten=3D93631
         ->  Seq Scan on tf_transaction_item_person trans  (cost=3D0.00..22=
8945.93 rows=3D1542683 width=3D199) (actual time=3D13.312..52046.689 rows=
=3D1393515 loops=3D1)
               Filter: (general_ledger_code IS NULL)
               Rows Removed by Filter: 2014865
               Buffers: shared read=3D191731
         ->  Hash  (cost=3D1012542.32..1012542.32 rows=3D6833049 width=3D52=
) (actual time=3D152339.000..152339.000 rows=3D6909204 loops=3D1)
               Buckets: 524288  Batches: 16  Memory Usage: 39882kB
               Buffers: shared hit=3D120188 read=3D684747, temp written=3D5=
7588
               ->  Seq Scan on tf_purchases_person purch  (cost=3D0.00..101=
2542.32 rows=3D6833049 width=3D52) (actual time=3D8.252..140992.716 rows=3D=
6909204 loops=3D1)
                     Filter: (general_ledger_code IS NOT NULL)
                     Rows Removed by Filter: 13851527
                     Buffers: shared hit=3D120188 read=3D684747
 Planning time: 0.867 ms
 Execution time: 230328.223 ms
(18 rows)


with indexes


                                                                          Q=
UERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20
---------------------------------------------------------------------------=
---------------------------------------------------------------------------=
--------
 Update on tf_transaction_item_person trans  (cost=3D1161742.22..1567806.87=
 rows=3D497927 width=3D228) (actual time=3D155171.388..155171.388 rows=3D0 =
loops=3D1)
   Buffers: shared hit=3D88095 read=3D908571, temp read=3D93661 written=3D9=
3631
   ->  Hash Join  (cost=3D1161742.22..1567806.87 rows=3D497927 width=3D228)=
 (actual time=3D155171.358..155171.358 rows=3D0 loops=3D1)
         Hash Cond: ((trans.purchased_log_id)::text =3D (purch.purchased_lo=
g_id)::text)
         Buffers: shared hit=3D88095 read=3D908571, temp read=3D93661 writt=
en=3D93631
         ->  Seq Scan on tf_transaction_item_person trans  (cost=3D0.00..22=
8945.93 rows=3D1542683 width=3D199) (actual time=3D16.801..31016.221 rows=
=3D1393515 loops=3D1)
               Filter: (general_ledger_code IS NULL)
               Rows Removed by Filter: 2014865
               Buffers: shared read=3D191731
         ->  Hash  (cost=3D1012542.32..1012542.32 rows=3D6700872 width=3D53=
) (actual time=3D105101.946..105101.946 rows=3D6909204 loops=3D1)
               Buckets: 524288  Batches: 16  Memory Usage: 39882kB
               Buffers: shared hit=3D88095 read=3D716840, temp written=3D57=
588
               ->  Seq Scan on tf_purchases_person purch  (cost=3D0.00..101=
2542.32 rows=3D6700872 width=3D53) (actual time=3D13.823..95970.776 rows=3D=
6909204 loops=3D1)
                     Filter: (general_ledger_code IS NOT NULL)
                     Rows Removed by Filter: 13851527
                     Buffers: shared hit=3D88095 read=3D716840
 Planning time: 90.409 ms
 Execution time: 155179.181 ms
(18 rows)

Thanks
Armand


On Apr 19, 2017, at 3:06 AM, Albe Laurenz <laurenz.albe@wien.gv.at> wrote:

> Armand Pirvu wrote:
>> Running 9.5.2
>>=20
>> I have the following update and run into a bit of a trouble . I realize =
the tables
>> involved have quite some data but here goes
>>=20
>>=20
>> UPDATE
>>    tf_transaction_item_person TRANS
>> SET
>>    general_ledger_code =3D PURCH.general_ledger_code,
>>    general_ledger_code_desc =3D PURCH.general_ledger_code_desc,
>>    update_datetime =3D now()::timestamp(0)
>> FROM
>>   tf_purchases_person PURCH
>> WHERE
>>    PURCH.general_ledger_code !=3D '' AND
>>    TRANS.purchased_log_id =3D PURCH.purchased_log_id AND
>>    TRANS.general_ledger_code !=3D PURCH.general_ledger_code
>> ;
> [...]
>>                              Table "tf_transaction_item_person"
> [...]
>> Indexes:
>>    "tf_transaction_item_person_pkey" PRIMARY KEY, btree (person_transact=
ion_item_id)
>>    "tf_tip_idx" btree (client_id, update_datetime)
>>    "tf_tip_isdel_idx" btree (show_id, person_transaction_item_id)
>=20
> You don't show EXPLAIN (ANALYZE, BUFFERS) output for the problematic quer=
y,
> so it is difficult to say where the time is spent.
>=20
> But since you say that the same query without the UPDATE also takes more =
than
> a minute, the duration for the UPDATE is not outrageous.
> It may well be that much of the time is spent updating the index
> entries for the 3.5 million affected rows.
>=20
> I don't know if dropping indexes for the duration of the query and recrea=
ting
> them afterwards would be a net win, but you should consider it.
>=20
> It may be that the only ways to improve performance would be general
> things like faster I/O, higher max_wal_size setting, and, most of all,
> enough RAM in the machine to contain the whole database.
>=20
> Yours,
> Laurenz Albe


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