Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1d0ayq-0002cf-Ua for pgsql-performance@arkaria.postgresql.org; Tue, 18 Apr 2017 21:48:13 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1d0ayq-0007K9-Ce for pgsql-performance@arkaria.postgresql.org; Tue, 18 Apr 2017 21:48:12 +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 1d0ax3-0004Dk-U3 for pgsql-performance@postgresql.org; Tue, 18 Apr 2017 21:46:22 +0000 Received: from mail-it0-x22e.google.com ([2607:f8b0:4001:c0b::22e]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1d0ax0-0002tL-Fa for pgsql-performance@postgresql.org; Tue, 18 Apr 2017 21:46:20 +0000 Received: by mail-it0-x22e.google.com with SMTP id a140so2916740ita.0 for ; Tue, 18 Apr 2017 14:46:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:content-transfer-encoding:subject:message-id:date:to :mime-version; bh=AbljGX/wWO7z0uxvLQIncc+oYApv9GnuXD/uILjVQi8=; b=u5Dz01xyAyMZSnIFaHylZPqlZatKTlsv/iF1MucX9QiaRV7CwAXQ0QQvTVig7UkKQa 9gVe9TTsjNrtG2cGQOjgZ9c/1WhZyHmBlO0J2ZEdv0cT8g2XLBiw/Qsp9KAHp8pOuBZI CmbH/fKUuNjFLsZuWu9KPpAng9EzM3Sb7zf5Q6VBiCkvdr9XvX/rlfo+nl7Sp0aRDlzN +AyrWJbb+89byG+tyXTzvqLTpMRBL4mbkNag8cZm58afgQnp9iZ97pNYWayeOezcUVW4 RW42FIituKFgJ5BnWEoiaw7htLOdRkRvtEcB02PM2lxCOU5K1U49x5SDrqvqg7yMMgf1 0zMQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:content-transfer-encoding:subject :message-id:date:to:mime-version; bh=AbljGX/wWO7z0uxvLQIncc+oYApv9GnuXD/uILjVQi8=; b=coKtzlA6roR6kQzwUqLbEVB/UwOym4xWP9TPycIKmKrxwQJDy3HOvOuswxsQFvhjCX aBBs1bdRQO9iukeIjY8KQksnrIgi+9+AIG3/+eO0F41JmcB7GbieAfABiThvZNB1ttLN ChKxpaLBV/U62apO7hnWHSFkauW+4Ddmz0uHGu9Jus3QGptZAe0aKLIH+y3Y3mOvjFbG hX6JSRNaVy64WjThX5e5b2bo0k1gj/CgDcMfYrSRyUSAYqt+CEL6D+T6lU76UXYBYobB eOBjf+vwLlU1C1ZEavuPoc5peJQXQIvgzRGtlgWNZvylDX9aopT6QGWj50o2IAOBUx1t 8JlA== X-Gm-Message-State: AN3rC/5W2z66vuNoHzBVpwxFJCWAQ2w605NiUWm4OiqgTvHbKN0ovUq1 alQN22dB8Ze/kDTl X-Received: by 10.36.200.10 with SMTP id w10mr5531852itf.86.1492551976856; Tue, 18 Apr 2017 14:46:16 -0700 (PDT) Received: from ?IPv6:2605:a601:cd1:d300:f889:98b0:20c2:da7? ([2605:a601:cd1:d300:f889:98b0:20c2:da7]) by smtp.gmail.com with ESMTPSA id k22sm5468738iti.5.2017.04.18.14.46.15 for (version=TLS1 cipher=ECDHE-RSA-AES128-SHA bits=128/128); Tue, 18 Apr 2017 14:46:16 -0700 (PDT) From: "Armand Pirvu (home)" Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Subject: update from performance question Message-Id: <38D1728F-B930-44B7-92EA-29225176E238@gmail.com> Date: Tue, 18 Apr 2017 16:46:09 -0500 To: pgsql-performance@postgresql.org Mime-Version: 1.0 (Mac OS X Mail 7.3 \(1878.6\)) X-Mailer: Apple Mail (2.1878.6) 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 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 here goes 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 ; QUERY 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 ---------------------------------------------------------------------------= ------------------------------ Update on tf_transaction_item_person trans (cost=3D1432701.45..2209776.18= rows=3D3405170 width=3D231) -> Hash Join (cost=3D1432701.45..2209776.18 rows=3D3405170 width=3D231) Hash Cond: ((trans.purchased_log_id)::text =3D (purch.purchased_lo= g_id)::text) Join Filter: ((trans.general_ledger_code)::text <> (purch.general_= ledger_code)::text) -> Seq Scan on tf_transaction_item_person trans (cost=3D0.00..16= 0488.20 rows=3D3405920 width=3D257) -> Hash (cost=3D970842.28..970842.28 rows=3D20743134 width=3D56) -> Seq Scan on tf_purchases_person purch (cost=3D0.00..970= 842.28 rows=3D20743134 width=3D56) Filter: ((general_ledger_code)::text <> ''::text) Table "tf_transaction_item_person" Column | Type | = Modifiers=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------+-----------------------------+-----------= ----------------------------- person_transaction_item_id | character varying(100) | not null person_transaction_id | character varying(100) | not null transaction_id | character varying(100) |=20 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 |=20 is_deleted | character varying(5) |=20 reg_is_deleted | character varying(5) | not null d= efault ''::character varying birst_is_deleted | character varying(5) | not null d= efault ''::character varying general_ledger_code | character varying(20) |=20 general_ledger_code_desc | character varying(50) |=20 Indexes: "tf_transaction_item_person_pkey" PRIMARY KEY, btree (person_transactio= n_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=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 -----------------------------+-----------------------------+---------------= ------------------------- purchased_log_id | character varying(100) | not null show_id | character varying(100) |=20 client_id | integer |=20 company_id | integer |=20 person_id | integer |=20 badge_id | character varying(100) |=20 item_id | character varying(100) |=20 general_ledger_code | character varying(100) |=20 purchase_status | character varying(100) |=20 purchase_quantity | integer |=20 purchase_rate | double precision |=20 purchase_total | double precision |=20 tax_rate | double precision |=20 tax_total | double precision |=20 final_total | double precision |=20 add_by_user_id | character varying(100) |=20 add_date | timestamp without time zone |=20 purchase_item_person_source | character varying(1) |=20 is_deleted | character varying(5) |=20 update_datetime | timestamp without time zone |=20 reg_is_deleted | character varying(5) | not null defau= lt ''::character varying birst_is_deleted | character varying(5) | not null defau= lt ''::character varying general_ledger_code_desc | character varying(50) |=20 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_co= de_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 amo= unt of records relative to the tables=92 counts and attempt some indexing birstdb=3D# select count(*) from tf_transaction_item_person; count=20=20 --------- 3405920 (1 row) birstdb=3D# select count(*) from tf_purchases_person; count=20=20=20 ---------- 20747702 (1 row) select count(TRANS.purchased_log_id) from tf_transaction_item_person TRANS, tf_purchases_person PURCH WHERE PURCH.general_ledger_code !=3D '' AND TRANS.show_id =3D PURCH.show_id AND TRANS.purchased_log_id =3D PURCH.purchased_log_id AND TRANS.general_ledger_code !=3D PURCH.general_ledger_code ; count=20 ------- 0 select count(TRANS.purchased_log_id) from tf_transaction_item_person TRANS, tf_purchases_person PURCH WHERE TRANS.show_id =3D PURCH.show_id AND TRANS.purchased_log_id =3D PURCH.purchased_log_id AND TRANS.general_ledger_code !=3D PURCH.general_ledger_code ; count=20 ------- 0 create index foo1 on tf_purchases_person (general_ledger_code, show_id, pur= chased_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 =3D PURCH.general_ledger_code, general_ledger_code_desc =3D PURCH.general_ledger_code_desc, update_datetime =3D now()::timestamp(0) FROM ( select a.show_id ,a.general_ledger_code, a.purchased_log_id, a.general_ledg= er_code_desc from=20 tf_transaction_item_person a left join tf_purchases_person b on b.general_ledger_code !=3D '' AND b.show_id=3Da.show_id AND b.purchased_log_id =3D a.purchased_log_id AND b.general_ledger_code =3D a.general_ledger_code where b.general_ledger_code is null ) PURCH WHERE TRANS.purchased_log_id =3D PURCH.purchased_log_id AND TRANS.show_id =3D PURCH.show_id ; = QUERY 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=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=3D19194432.16..19467044.= 63 rows=3D34859 width=3D387) -> Nested Loop Anti Join (cost=3D19194432.16..19467044.63 rows=3D34859= width=3D387) -> Merge Join (cost=3D19194431.59..19254383.78 rows=3D34859 widt= h=3D415) Merge Cond: (((trans.show_id)::text =3D (a.show_id)::text) A= ND ((trans.purchased_log_id)::text =3D (a.purchased_log_id)::text)) -> Sort (cost=3D9603638.01..9612152.81 rows=3D3405920 widt= h=3D199) Sort Key: trans.show_id, trans.purchased_log_id -> Index Scan using tf_tip_isdel_idx on tf_transactio= n_item_person trans (cost=3D0.56..8908143.78 rows=3D3405920 width=3D199) -> Materialize (cost=3D9590793.59..9607823.19 rows=3D34059= 20 width=3D216) -> Sort (cost=3D9590793.59..9599308.39 rows=3D340592= 0 width=3D216) Sort Key: a.show_id, a.purchased_log_id -> Index Scan using foo2 on tf_transaction_item= _person a (cost=3D0.56..8872017.35 rows=3D3405920 width=3D216) -> Index Scan using foo1 on tf_purchases_person b (cost=3D0.56..= 6.09 rows=3D1 width=3D46) Index Cond: (((general_ledger_code)::text =3D (a.general_led= ger_code)::text) AND ((show_id)::text =3D (a.show_id)::text) AND ((purchase= d_log_id)::text =3D (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=20 = QUERY 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=20=20=20=20=20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- Merge Anti Join (cost=3D1.12..15466467.80 rows=3D3405920 width=3D176) (ac= tual time=3D245.940..63987.645 rows=3D3405920 loops=3D1) Merge Cond: ((trans.general_ledger_code)::text =3D (purch.general_ledger= _code)::text) Join Filter: ((trans.purchased_log_id)::text =3D (purch.purchased_log_id= )::text) -> Index Scan using foo2 on tf_transaction_item_person trans (cost=3D0= .56..8162817.35 rows=3D3405920 width=3D200) (actual time=3D245.928..59480.4= 44 rows=3D3405920 loops=3D1) -> Index Only Scan using foo1 on tf_purchases_person purch (cost=3D0.5= 6..7243277.80 rows=3D20743134 width=3D30) (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 --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance