Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kVOXc-0005Te-Mk for pgsql-performance@arkaria.postgresql.org; Thu, 22 Oct 2020 00:33:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kVOX3-0001xG-KY for pgsql-performance@arkaria.postgresql.org; Thu, 22 Oct 2020 00:32:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kVOX3-0001x9-28 for pgsql-performance@lists.postgresql.org; Thu, 22 Oct 2020 00:32:41 +0000 Received: from sonic301-30.consmr.mail.ne1.yahoo.com ([66.163.184.199]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kVOWy-0003Yo-NX for pgsql-performance@lists.postgresql.org; Thu, 22 Oct 2020 00:32:40 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1603326752; bh=AUKWrmx0Ld/ZCIrzDFmLVIEx+IcaqT8q42oqmvnJPX8=; h=Date:From:To:Subject:References:From:Subject; b=JS+Q0QagM0sw0dbUaHqQEyK8MzLone0S3JHgEf+OHK5vmdKWK3symeMCi3ziMKYEZdWz8gloAA0ReZrOzShv5d6HNskifoQBuwoouXPfJA3mwxirm4ZQc2Z+e2qp6jzzzlLhN2/FOys8onSwInyWFsaTjwYQz8XBFoOwJOhl9XeOCScQstLtaOhQ5dtRsSDn16Hmha6mcPsp2NpC+RD74sWy+sFQQlWXqwQm/S4S979cgrgT+3vmQZGfP+X+jnNuWMPGpYaSDlE/gCyngtUuVwaCPWcYu+F+6CXBiqWjItEJHOg3Qz6TaIUeHiXCECJpqhYv9doIYlYTOv4rtCjIOQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1603326752; bh=sTpPu1+rbRj9QqQ5aJH5Qw+gXPUDoF4c8X1K44v385K=; h=Date:From:To:Subject; b=maNmtFsGTjDy+RBAzUv4RkO1nNOufW65EBlnvP1R/Igp2PiAKcOHzoqn0Ry05JxRuVtJNjf16J9kU9yPczH8lJuEupVgIM2GgaBCM+JMpxlV1N9vO+s+2FegvxCNnXMY09t/OaqHypaEcZ8dIkBq6stc5yYEiNn+fts2rtEWUaEkoSJwGxTLkVQndnVM4Gcw/fjU2QgXOVJce7PlATiQL/7QEzhgYmeZ+J3DW/TmU1t8j/gLqDVZpWh4B09FBmN3P0FU9nE8JkwvO04Ud6v1A5XIo3a0SY4PPAKePqk7ljFC2+Eau06L71lMys21xsRlwzHF5POkT4qOSAEVlTNV+w== X-YMail-OSG: qDLOCN4VM1k79VXEqMzhFPaMlPVTpuLmt8hzkYzB1scEFsD8AZyLJ8MmrGA6POY Yhy89zhFWUW8DPUvPUSPrRORMeXKRMgZlB7Z_6bW912Uv69B4Pkfs_yrc0rkQ.vWgkAsJotMcbuS B03Snll_Wsns4EhmfCWVUve3LxMC7zoni6Uq.nyeHPrS8tdaiZ.X6XKK8QYlO3ouc8M85msmFT8. f3IKISbPwYsLttmHQdaesq5ToAPxrcpN1jcuZGTCdlxCB_Y_x8RvgMjRjYI2kEG71cps79OYkRo3 RsatNy1z_U2SnjXbSvIwhRgjv1EIqKmVc8YDp_9SwdA76Z4wqsjYMVB_FT4uXTqgRAE0QMl2lYxQ qdPY5A666oCQgQlrUHT8QxtG2TiMZHJ83YvwHWnBkQccnt3WYyrjiwNHgDYkilcQlN2Bmpl8jR.e Xw7LH8rToHjaFKz4l03X4f5x86V8AT3wNfL1CtQI9EMRqqvd1SbXmLJcajiFN8Sy_5vmSN5H5GR8 j8z6YUyJG1wFAOAiOaDDxDxq7obrBdkIFw.uk2aZZF3D.7C5lDcgoNp2DJzOfVen0nb0e6CS1uzt UX_9vZG83Y6MY7yAmjLdiCitHP0_Aq85nqSWvh3Bh7anEMLE_5DsSTR3Cgw3TXwR0jgHEwdw43x. owB351P.vmMCXsD3u61LbDrZO9IVdZrrfqhMQonyuG7pbUKo5gbsXl8NLCXkxDE4W9F2CtqT5ppx 8M9UxuYD_L9dobCSoN_EUM9ckUeRRWprWhHBQpZfKo.BkLWaanywcfxp2LvNL.Bk_R83Km_JtOmH r1V67bvCoibeZ.pq_B2FDNB_O6OP5pTvDBHmMZ3stvur9psfdz56ipr40bHmgWGRrF8ZZV2Ut_va HJIJNIUePjSpkiGg8kIjI0S.AB7uQQqtfEIS6tAlAAIYKP0BNyDgbrlNrM6Ie3hw2Kv_xLH_90vJ _Vlv3Tge_qNAdKMcORSJfHw7kTCHkqt8HxUEAPHUz1ihHWhMo26G5Zkbm71hSnZIUeNICJ3DuVdA XZ0DY79rE3W3sudyYocjvNhHTlWC8Snt9r21zkuSyW.A2Vn_.7g5T0Jx4pxn2761J5b4pgD1akJY cuUBpIb1i8q3SCBTpcDO8q8TY8UW5jAgWbuK5rjwhUVwUCi_d7jCb.PvPombUWjEu1PNIbRXjrl6 nW7bdJmmhfLUl.5PGE4uXWDUQe.gx9OvHRQjnxWiLKCGcKrHChINgp1tidzWl9eesA_0jSobz.WI hd1BET4Cdji_F6nVTqZbaIJbHb4ztU499DY39BpIHb.YyRzXkY6YAyM9zscUMP175rRlrzCLkAo5 i9rvN1.bKDmMRu4OhNBQxYigvE62LxK1u..LWo.kQqj4VjAHaHT6XZ1a95U5cDnOYbdagoRj7jMC 2t4OI7TlB2QN4lzp2uHeXrRPBZXauh_jwHZlOZ2SClJKxJ_dC5ueIsL2hnuV.uZ8D Received: from sonic.gate.mail.ne1.yahoo.com by sonic301.consmr.mail.ne1.yahoo.com with HTTP; Thu, 22 Oct 2020 00:32:32 +0000 Date: Thu, 22 Oct 2020 00:32:29 +0000 (UTC) From: Nagaraj Raj To: Pgsql Performance Message-ID: <2026306342.2139401.1603326749019@mail.yahoo.com> Subject: Query performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2139400_360393687.1603326749015" References: <2026306342.2139401.1603326749019.ref@mail.yahoo.com> X-Mailer: WebService/1.1.16868 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.80 Safari/537.36 Content-Length: 28538 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2139400_360393687.1603326749015 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, I have long running query which running for long time and its planner a= lways performing sequnce scan the table2.My gole is to reduce Read IO on th= e disk cause, this query runns=C2=A0more oftenly=C2=A0( using this in funti= on for ETL).=C2=A0 table1:=C2=A0transfer_order_header(records=C2=A02782678)table2:=C2=A0transf= er_order_item ( records: 15995697)here is the query: set work_mem =3D '688552kB';explain (analyze,buffers)select=C2=A0 =C2=A0 = =C2=A0COALESCE(itm.serialnumber,'') AS SERIAL_NO,=C2=A0=C2=A0 =C2=A0 =C2=A0= =C2=A0 =C2=A0 =C2=A0 COALESCE(itm.ITEM_SKU,'') AS SKU,=C2=A0=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 COALESCE(itm.receivingplant,'') AS RECEIVINGPLA= NT,=C2=A0 COALESCE(itm.STO_ID,'') AS STO, supplyingplant,=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 COALESCE(itm.deliveryitem,'') AS DELIVERYITEM, =C2= =A0 =C2=A0 min(eventtime) as eventtime=C2=A0=C2=A0FROM sor_t.transfer_order= _header hed,sor_t.transfer_order_item itm=C2=A0=C2=A0where hed.eventid=3Dit= m.eventid group by 1,2,3,4,5,6 Query Planner[2]: "Finalize GroupAggregate (cost=3D1930380.06..4063262.11 rows=3D16004137 wid= th=3D172) (actual time=3D56050.500..83268.566 rows=3D15891873 loops=3D1)"" = Group Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(i= tm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::cha= racter varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.supply= ingplant, (COALESCE(itm.deliveryitem, ''::character varying))"" Buffers: sh= ared hit=3D712191 read=3D3, temp read=3D38232 written=3D38233"" -> Gather M= erge (cost=3D1930380.06..3669827.09 rows=3D13336780 width=3D172) (actual ti= me=3D56050.488..77106.993 rows=3D15948520 loops=3D1)"" Workers Planned: 2""= Workers Launched: 2"" Buffers: shared hit=3D2213081 read=3D12, temp read= =3D142840 written=3D142843"" -> Partial GroupAggregate (cost=3D1929380.04..= 2129431.74 rows=3D6668390 width=3D172) (actual time=3D50031.458..54888.828 = rows=3D5316173 loops=3D3)"" Group Key: (COALESCE(itm.serialnumber, ''::char= acter varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALESCE= (itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::cha= racter varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::chara= cter varying))"" Buffers: shared hit=3D2213081 read=3D12, temp read=3D14284= 0 written=3D142843"" -> Sort (cost=3D1929380.04..1946051.01 rows=3D6668390 = width=3D172) (actual time=3D50031.446..52823.352 rows=3D5332010 loops=3D3)"= " Sort Key: (COALESCE(itm.serialnumber, ''::character varying)), (COALESCE(= itm.item_sku, ''::character varying)), (COALESCE(itm.receivingplant, ''::ch= aracter varying)), (COALESCE(itm.sto_id, ''::character varying)), hed.suppl= yingplant, (COALESCE(itm.deliveryitem, ''::character varying))"" Sort Metho= d: external merge Disk: 305856kB"" Worker 0: Sort Method: external merge Di= sk: 436816kB"" Worker 1: Sort Method: external merge Disk: 400048kB"" Buffe= rs: shared hit=3D2213081 read=3D12, temp read=3D142840 written=3D142843"" -= > Parallel Hash Join (cost=3D133229.66..603743.97 rows=3D6668390 width=3D17= 2) (actual time=3D762.925..3901.133 rows=3D5332010 loops=3D3)"" Hash Cond: = ((itm.eventid)::text =3D (hed.eventid)::text)"" Buffers: shared hit=3D22130= 27 read=3D12"" -> Parallel Seq Scan on transfer_order_item itm (cost=3D0.00= ..417722.90 rows=3D6668390 width=3D68) (actual time=3D0.005..524.359 rows= =3D5332010 loops=3D3)"" Buffers: shared hit=3D351039"" -> Parallel Hash (co= st=3D118545.68..118545.68 rows=3D1174718 width=3D35) (actual time=3D755.590= ..755.590 rows=3D926782 loops=3D3)"" Buckets: 4194304 Batches: 1 Memory Usa= ge: 243808kB"" Buffers: shared hit=3D1861964 read=3D12"" -> Parallel Index = Only Scan using transfer_order_header_eventid_supplyingplant_eventtime_idx1= on transfer_order_header hed (cost=3D0.56..118545.68 rows=3D1174718 width= =3D35) (actual time=3D0.128..388.436 rows=3D926782 loops=3D3)"" Heap Fetche= s: 18322"" Buffers: shared hit=3D1861964 read=3D12""Planning Time: 1.068 ms= ""Execution Time: 84274.004 ms" Tables[1]=C2=A0 created ddls in dbfiddle. PG Server:=C2=A0 PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (G= CC) 4.9.3, 64-bit.RAM: 456Mem Settings:=C2=A0"maintenance_work_mem" "856371= 2" "kB" "work_mem" "688552" =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0"kB" "wal_buffers" =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0"2048"=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0 "8kB" "shared_buffers" =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0"44388442"=C2=A0 =C2=A0 =C2=A0"8kB" Any suggestions would greatly appretiated.=C2=A0 Thanks,Rj ------=_Part_2139400_360393687.1603326749015 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi, I have long running query which running = for long time and its planner always performing sequnce scan the table2.My = gole is to reduce Read IO on the disk cause, this query runns more oft= enly ( using this in funtion for ETL). 


table1: transfer_order_header(records 2782678)
<= div dir=3D"ltr" data-setdir=3D"false">table2: transfer_order_item ( records: 15995697)
here is the query:

set work_m= em =3D '688552kB';
explain (analyze,buffers)
select&nbs= p;    COALESCE(itm.serialnumber,'') AS SERIAL_NO, 
            COALESCE(itm.ITEM_SKU,'') AS SKU= , 
            COALESCE(itm.re= ceivingplant,'') AS RECEIVINGPLANT, 
=09=09=09COALESCE(itm.STO_ID,'') AS STO,
<= span style=3D"white-space: pre-wrap;">=09=09=09supplyingplant,
=
            COALESCE(itm.deliveryitem,''= ) AS DELIVERYITEM,
=09=09<= /span>    min(eventtime) as eventtime 
 FROM = sor_t.transfer_order_header hed,sor_t.transfer_order_item itm 
 where hed.eventid=3Ditm.eventid group by 1,2,3,4,5,6

Query Planner[2]:

= "Finalize GroupAggregate (cost=3D1930380.06..4063262.= 11 rows=3D16004137 width=3D172) (actual time=3D56050.500..83268.566 rows=3D= 15891873 loops=3D1)" " Group Key: (COALESCE(itm.serialnumber, ''::c= haracter varying)), (COALESCE(itm.item_sku, ''::character varying)), (COALE= SCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.sto_id, ''::= character varying)), hed.supplyingplant, (COALESCE(itm.deliveryitem, ''::ch= aracter varying))" " Buffers: shared hit=3D712191 read=3D3, temp = read=3D38232 written=3D38233" " -> Gather Merge (cost=3D1930380.06..366= 9827.09 rows=3D13336780 width=3D172) (actual time=3D56050.488..77106.993 ro= ws=3D15948520 loops=3D1)" " Workers Planned: 2" " Workers Launched: 2" " Buffers: shared hit=3D2213081 read=3D1= 2, temp read=3D142840 written=3D142843" " -> Partial GroupAggregate (cost= =3D1929380.04..2129431.74 rows=3D6668390 width=3D172) (actual time=3D50031.= 458..54888.828 rows=3D5316173 loops=3D3)" " Group Key: (COALESCE(itm.serialn= umber, ''::character varying)), (COALESCE(itm.item_sku, ''::character varyi= ng)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE(itm.= sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.delivery= item, ''::character varying))" " Buffers: shared hit=3D2213081 re= ad=3D12, temp read=3D142840 written=3D142843" " -> Sort (cost=3D1929380.04.= .1946051.01 rows=3D6668390 width=3D172) (actual time=3D50031.446..52823.352= rows=3D5332010 loops=3D3)" " Sort Key: (COALESCE(itm.se= rialnumber, ''::character varying)), (COALESCE(itm.item_sku, ''::character = varying)), (COALESCE(itm.receivingplant, ''::character varying)), (COALESCE= (itm.sto_id, ''::character varying)), hed.supplyingplant, (COALESCE(itm.del= iveryitem, ''::character varying))" " Sort Method: external merg= e Disk: 305856kB" " Worker 0: Sort Method: ex= ternal merge Disk: 436816kB" " Worker 1: Sort Method: ex= ternal merge Disk: 400048kB" " Buffers: shared hit=3D2213= 081 read=3D12, temp read=3D142840 written=3D142843" " -> Parallel Hash Join = (cost=3D133229.66..603743.97 rows=3D6668390 width=3D172) (actual time=3D76= 2.925..3901.133 rows=3D5332010 loops=3D3)" " Hash Cond: ((itm.eve= ntid)::text =3D (hed.eventid)::text)" " Buffers: shared hit= =3D2213027 read=3D12" " -> Parallel Seq Scan on transfer_order= _item itm (cost=3D0.00..417722.90 rows=3D6668390 width=3D68) (actua= l time=3D0.005..524.359 rows=3D5332010 loops=3D3)" " Buffers: share= d hit=3D351039" " -> Parallel Hash= (cost=3D118545.68..118545.68 rows=3D1174718 width=3D35) (actual time=3D75= 5.590..755.590 rows=3D926782 loops=3D3)" " Buckets: 41943= 04 Batches: 1 Memory Usage: 243808kB" " Buffers: share= d hit=3D1861964 read=3D12" " -> Paralle= l Index Only Scan using transfer_order_header_eventid_supplyingplant_eventt= ime_idx1 on transfer_order_header hed (cost=3D0.56..118545.68 rows=3D11747= 18 width=3D35) (actual time=3D0.128..388.436 rows=3D926782 loops=3D3)" " Heap Fet= ches: 18322" " Buffers:= shared hit=3D1861964 read=3D12" "Planning Time: 1.068 ms" "Execution Time: 84274.004 ms"

=

Tables[1]&= nbsp; created ddls in dbfiddle.


<= br>
PG Server:&nb= sp; PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9= .3, 64-bit.
RAM: 4= 56
Mem Setti= ngs: 
= "maintenance_work_mem"=09"8563712"=09"kB"
=
"work= _mem"=09 "688552"=09   &nbs= p;    "kB"
"wal_buffers"=09      &nb= sp;            =      "2048"     &n= bsp;        "8kB"
"shared_buffers""8kB"
Any suggestions= would greatly appretiated. 



= Thanks,
Rj


<= /div>

------=_Part_2139400_360393687.1603326749015--