[PERFORM] update from performance question

Hi


Running 9.5.2

I have the following update and run into a bit of a trouble . I realize the tables involved have quite some data but
heregoes 


UPDATE
    tf_transaction_item_person TRANS
SET
    general_ledger_code = PURCH.general_ledger_code,
    general_ledger_code_desc = PURCH.general_ledger_code_desc,
    update_datetime = now()::timestamp(0)
FROM
   tf_purchases_person PURCH
WHERE
    PURCH.general_ledger_code != '' AND
    TRANS.purchased_log_id = PURCH.purchased_log_id AND
    TRANS.general_ledger_code != PURCH.general_ledger_code
;


                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Update on tf_transaction_item_person trans  (cost=1432701.45..2209776.18 rows=3405170 width=231)
   ->  Hash Join  (cost=1432701.45..2209776.18 rows=3405170 width=231)
         Hash Cond: ((trans.purchased_log_id)::text = (purch.purchased_log_id)::text)
         Join Filter: ((trans.general_ledger_code)::text <> (purch.general_ledger_code)::text)
         ->  Seq Scan on tf_transaction_item_person trans  (cost=0.00..160488.20 rows=3405920 width=257)
         ->  Hash  (cost=970842.28..970842.28 rows=20743134 width=56)
               ->  Seq Scan on tf_purchases_person purch  (cost=0.00..970842.28 rows=20743134 width=56)
                     Filter: ((general_ledger_code)::text <> ''::text)





                              Table "tf_transaction_item_person"
             Column              |            Type             |               Modifiers
---------------------------------+-----------------------------+----------------------------------------
 person_transaction_item_id      | character varying(100)      | not null
 person_transaction_id           | character varying(100)      | not null
 transaction_id                  | character varying(100)      |
 show_id                         | character varying(100)      | not null
 client_id                       | integer                     | not null
 company_id                      | integer                     | not null
 person_id                       | integer                     | not null
 badge_id                        | character varying(100)      | not null
 transaction_type_code           | character varying(100)      | not null
 payment_type_code               | character varying(100)      | not null
 purchased_log_id                | character varying(100)      | not null
 item_id                         | character varying(100)      | not null
 transaction_amount              | double precision            | not null
 add_by_user_id                  | character varying(100)      | not null
 add_date                        | timestamp without time zone | not null
 transaction_items_person_source | character varying(1)        | not null
 update_datetime                 | timestamp without time zone |
 is_deleted                      | character varying(5)        |
 reg_is_deleted                  | character varying(5)        | not null default ''::character varying
 birst_is_deleted                | character varying(5)        | not null default ''::character varying
 general_ledger_code             | character varying(20)       |
 general_ledger_code_desc        | character varying(50)       |
Indexes:
    "tf_transaction_item_person_pkey" PRIMARY KEY, btree (person_transaction_item_id)
    "tf_tip_idx" btree (client_id, update_datetime)
    "tf_tip_isdel_idx" btree (show_id, person_transaction_item_id)


                               Table "tf_purchases_person"
           Column            |            Type             |               Modifiers
-----------------------------+-----------------------------+----------------------------------------
 purchased_log_id            | character varying(100)      | not null
 show_id                     | character varying(100)      |
 client_id                   | integer                     |
 company_id                  | integer                     |
 person_id                   | integer                     |
 badge_id                    | character varying(100)      |
 item_id                     | character varying(100)      |
 general_ledger_code         | character varying(100)      |
 purchase_status             | character varying(100)      |
 purchase_quantity           | integer                     |
 purchase_rate               | double precision            |
 purchase_total              | double precision            |
 tax_rate                    | double precision            |
 tax_total                   | double precision            |
 final_total                 | double precision            |
 add_by_user_id              | character varying(100)      |
 add_date                    | timestamp without time zone |
 purchase_item_person_source | character varying(1)        |
 is_deleted                  | character varying(5)        |
 update_datetime             | timestamp without time zone |
 reg_is_deleted              | character varying(5)        | not null default ''::character varying
 birst_is_deleted            | character varying(5)        | not null default ''::character varying
 general_ledger_code_desc    | character varying(50)       |
Indexes:
    "tf_purchases_person_pkey" PRIMARY KEY, btree (purchased_log_id)
    "foo1" btree (general_ledger_code, show_id, purchased_log_id)
    "tf_pp_genl_idx" btree (show_id, general_ledger_code, general_ledger_code_desc)
    "tf_pp_idx" btree (client_id, update_datetime)
    "tf_pp_isdel_idx" btree (show_id, purchased_log_id)



I looked at the counts to see which conditions are getting me the least amount of records relative to the tables’
countsand attempt some indexing 


birstdb=# select count(*) from tf_transaction_item_person;
  count
---------
 3405920
(1 row)
birstdb=# select count(*) from tf_purchases_person;
  count
----------
 20747702
(1 row)
select count(TRANS.purchased_log_id)
from

    tf_transaction_item_person TRANS,
    tf_purchases_person PURCH
WHERE
    PURCH.general_ledger_code != '' AND
    TRANS.show_id = PURCH.show_id AND
    TRANS.purchased_log_id = PURCH.purchased_log_id AND
    TRANS.general_ledger_code != PURCH.general_ledger_code
;
 count
-------
     0

select count(TRANS.purchased_log_id)
from

    tf_transaction_item_person TRANS,
    tf_purchases_person PURCH
WHERE
    TRANS.show_id = PURCH.show_id AND
    TRANS.purchased_log_id = PURCH.purchased_log_id AND
    TRANS.general_ledger_code != PURCH.general_ledger_code
;
 count
-------
     0




create index foo1 on tf_purchases_person (general_ledger_code, show_id, purchased_log_id);
create index foo2 on tf_transaction_item_person (general_ledger_code, show_id, purchased_log_id);



No real improvement

I went even this route


UPDATE
     tf_transaction_item_person TRANS
SET
    general_ledger_code = PURCH.general_ledger_code,
    general_ledger_code_desc = PURCH.general_ledger_code_desc,
    update_datetime = now()::timestamp(0)
FROM
(
select a.show_id ,a.general_ledger_code, a.purchased_log_id, a.general_ledger_code_desc
from
tf_transaction_item_person a left join tf_purchases_person b
on
    b.general_ledger_code != '' AND
    b.show_id=a.show_id AND
    b.purchased_log_id = a.purchased_log_id AND
    b.general_ledger_code = a.general_ledger_code
where b.general_ledger_code is null
) PURCH
WHERE
    TRANS.purchased_log_id = PURCH.purchased_log_id AND
    TRANS.show_id = PURCH.show_id
;

                                                                                           QUERY PLAN
                                                        


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------
 Update on tf_transaction_item_person trans  (cost=19194432.16..19467044.63 rows=34859 width=387)
   ->  Nested Loop Anti Join  (cost=19194432.16..19467044.63 rows=34859 width=387)
         ->  Merge Join  (cost=19194431.59..19254383.78 rows=34859 width=415)
               Merge Cond: (((trans.show_id)::text = (a.show_id)::text) AND ((trans.purchased_log_id)::text =
(a.purchased_log_id)::text))
               ->  Sort  (cost=9603638.01..9612152.81 rows=3405920 width=199)
                     Sort Key: trans.show_id, trans.purchased_log_id
                     ->  Index Scan using tf_tip_isdel_idx on tf_transaction_item_person trans  (cost=0.56..8908143.78
rows=3405920width=199) 
               ->  Materialize  (cost=9590793.59..9607823.19 rows=3405920 width=216)
                     ->  Sort  (cost=9590793.59..9599308.39 rows=3405920 width=216)
                           Sort Key: a.show_id, a.purchased_log_id
                           ->  Index Scan using foo2 on tf_transaction_item_person a  (cost=0.56..8872017.35
rows=3405920width=216) 
         ->  Index Scan using foo1 on tf_purchases_person b  (cost=0.56..6.09 rows=1 width=46)
               Index Cond: (((general_ledger_code)::text = (a.general_ledger_code)::text) AND ((show_id)::text =
(a.show_id)::text)AND ((purchased_log_id)::text = (a.purchased 
_log_id)::text))
               Filter: ((general_ledger_code)::text <> ''::text)
(14 rows)


explain analyze took well in excess of 10 minutes

The idea is an update needs to find the records to update to begin with.
The inner select with the above mentioned indexes runs in

                                                                              QUERY PLAN
                                               

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Merge Anti Join  (cost=1.12..15466467.80 rows=3405920 width=176) (actual time=245.940..63987.645 rows=3405920 loops=1)
   Merge Cond: ((trans.general_ledger_code)::text = (purch.general_ledger_code)::text)
   Join Filter: ((trans.purchased_log_id)::text = (purch.purchased_log_id)::text)
   ->  Index Scan using foo2 on tf_transaction_item_person trans  (cost=0.56..8162817.35 rows=3405920 width=200)
(actualtime=245.928..59480.444 rows=3405920 loops=1) 
   ->  Index Only Scan using foo1 on tf_purchases_person purch  (cost=0.56..7243277.80 rows=20743134 width=30) (never
executed)
         Filter: ((general_ledger_code)::text <> ''::text)
         Heap Fetches: 0
 Planning time: 216.738 ms
 Execution time: 64901.139 ms


as opposed to a good 5 minutes

The update itself




I am at a bit of a loss.

Any ideas / pointers as to what I could do to make things better ?



Thanks in advance


- Armand