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 1kEJBw-0003Hh-L8 for pgsql-performance@arkaria.postgresql.org; Fri, 04 Sep 2020 21:24:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEJBv-0007N8-J0 for pgsql-performance@arkaria.postgresql.org; Fri, 04 Sep 2020 21:24:15 +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 1kEJBv-0007N1-90 for pgsql-performance@lists.postgresql.org; Fri, 04 Sep 2020 21:24:15 +0000 Received: from sonic316-21.consmr.mail.ne1.yahoo.com ([66.163.187.147]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kEJBq-00083k-Fj for pgsql-performance@lists.postgresql.org; Fri, 04 Sep 2020 21:24:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1599254647; bh=FSqxWc8yTp5lPQp6wVqqFsOUJ7GxKUpqpfJzjgu9gi8=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject; b=KqjlT2H/X5WrQyZhXpIqedwG1vqZzGm/RlXsoYs8xYSUm20OwFbZBXF9oebuYkny2hPVu1E9TpOtnQ+XnaHFsam2Ajln44lhp2jJEsccqLLkSjuGk5N+8VMbCqqIsLB1cz04KV8/D4MW524iRsnTjkZ5fGfr2gLUdD7Oc9tu0WnDGKqW6moP2QXUDxVtgSnCSTFjhnhkWBJSyWdS8Yh15U25kx8s5q36TVxqPd43e9Ehx6RjRObuxdU7l8aO1bwf2qNI4i+ZXIab5hVQiYwU/pTCjgKf7yWs2OM5+4tIa+oTVeu1PztaDFvFOj9BOQP99wlXrXZWxbI7OtSCMAe5uA== X-YMail-OSG: PECf730VM1kOkB2S70yDz5eyOQlTwVikNoE2Bpdj_mvvN0DPWfQrOhwj.Fkg1Di A6Jon.U57Q.AGkaQ6o3dYRoR4Cl4VHN1BKOQ9I71cZsE98P4.Wq1hBLeUJkcWbHilOjbXaAp8cC. 5yACa.bpbN7WGCN3F88TrCNB5z7Y_s0ZQlVPyRvbNQ9e4SXHd4aSYHq0uCHDy7RrumwWUF8uJRY4 cRFqZSJu8YFi9_nMuFdIRBHk3N3ehh765M1flHDMBpUSZIHTPovkcJIStuwCBcHuE0Kzn23hQPJ1 biqVdgMUgS3jau.ZbLILHOjRPkUGleMTRk3vtRjFFIO6.sXJdEgP_vvD.uXCQucSkRz.VCYgJFNp GKzHoOAOh1Iqg.0LYPE2mx1LbRjrQy3HBW5gD4TCcpPnz9fUS4qXQT8Ro2zH7ZKPB4CfH0LxXhH8 f8rifQ_nrTMfhXbgNZmlXs4_qNvGlKenE_z0gSrG580XxKyBuz.bOrYB0FvqHbrf4yn5k9GiFUsi 7SsOxxWUFEe9Cc.Z2Xo.RBISVXx85wxgoWUMrwbjcSbGkkgAZCjP0anMrTy_q7y3LWey1Jao9qub AnkEQfDvQjsNUhPnj40wnXRiLlVHl2xe1r4kRSZz941puH1g6VLHZyPm_1l.icHQkTkEoyk0qDwk UWQHaKHJkGwtly9xNGbxEuAsq367hMoSoEH2pWoEQIJUqIpnrdBGJ1_1KhEXbG.rmjqNv9vBUrLK isiF9rER_cpNOWbDkoXKODZisRHlBNbLyBOuCk6EDx.rJi6i0esWRNfjHUgr8cVVDqgh8YrQ9hjZ c1boqDNIov8YBng5lwwFDN89L63yjU1LCdwo6I5IzuylbHoSULnMdLFKbk.Xyg8DCzD2vqqeoijI CWrJJO0VWlI_ZozjVzA4CSPGbnAlcUcrgYWyMpMBF3LDDY451nmxwmGjyuBwGps8E_CG1HeEs74V IjKRcRcphStoNS27WUuLuuYP89bcZudtbwXl93ElJ2EVZxUbbDDMnjL6lIsm55lmiC2SCa1OeiTV BzDVSumNVNdEWg3DqDiivtky_po.lCd3R3rQtV35Qc3IW87Ni3dLCdWBuUUH_kZ7hzLFt5XNIKBU Wa3Ylb6eUFXMIwNqIdXh65fmy1nmHrEfVlEq.1WQj27Hfj4pzIWNwsdbIJXOP8oL3PXGNaA_4zQH 3Eiy6aSsymjN_Xqz_A2COBHZoWDzLM6nK0f88ly4_NUx4_VHadd6731AyAk0leUEV5oX32w3umr9 qI893dm2bLxvPB.IXcnRuX4S8oCg25vLzlC30UDeMO8upzvFV3Nu1d4grmX8OCtKsG9Evu_xeoDs RBsuvY_r78.k0Nq0w0XyUXNa4akpQSAa4lg63DcynGS6ITxbJFQBCzC3fRgkMwTI5vyMDTrafyOk 6sxIGUQDaYvFrpjDmA9FclD5XRxw4z5yE_86df19yuBPj.z4UEg-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.ne1.yahoo.com with HTTP; Fri, 4 Sep 2020 21:24:07 +0000 Date: Fri, 4 Sep 2020 21:24:05 +0000 (UTC) From: Nagaraj Raj To: Pgsql Performance Message-ID: <499793818.3392649.1599254645140@mail.yahoo.com> In-Reply-To: <975305787.3395625.1599254321500@mail.yahoo.com> References: <975305787.3395625.1599254321500.ref@mail.yahoo.com> <975305787.3395625.1599254321500@mail.yahoo.com> Subject: Re: Query performance issue MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3392648_326413305.1599254645136" X-Mailer: WebService/1.1.16565 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.83 Safari/537.36 Content-Length: 18904 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3392648_326413305.1599254645136 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable query planner:SPJe | explain.depesz.com |=20 |=20 | |=20 SPJe | explain.depesz.com | | | On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj wrote: =20 =20 I have a query which will more often run on DB and very slow and it is do= ing 'seqscan'. I was trying to optimize it by adding indexes in different w= ays but nothing helps. Any suggestions? Query: EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime=C2=A0from = (select serial_no,receivingplant,sku,eventtime as r3_eventtime, row_number(= ) over (partition by serial_no order by eventtime desc) as mpos=C2=A0from r= eceiving_item_delivered_received=C2=A0where eventtype=3D'LineItemdetailsRec= eived'and replenishmenttype =3D 'DC2SWARRANTY'and coalesce(serial_no,'') <>= '') Rec where mpos =3D 1; Query Planner:=C2=A0 "Subquery Scan on rec=C2=A0 (cost=3D70835.30..82275.49 rows=3D1760 width=3D= 39) (actual time=3D2322.999..3451.783 rows=3D333451 loops=3D1)""=C2=A0 Filt= er: (rec.mpos =3D 1)""=C2=A0 Rows Removed by Filter: 19900""=C2=A0 ->=C2=A0= WindowAgg=C2=A0 (cost=3D70835.30..77875.42 rows=3D352006 width=3D47) (actu= al time=3D2322.997..3414.384 rows=3D353351 loops=3D1)""=C2=A0 =C2=A0 =C2=A0= =C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D70835.30..71715.31 rows=3D352006 width= =3D39) (actual time=3D2322.983..3190.090 rows=3D353351 loops=3D1)""=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: receiving_item_delivere= d_received.serial_no, receiving_item_delivered_received.eventtime DESC""=C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: external merge= =C2=A0 Disk: 17424kB""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->= =C2=A0 Seq Scan on receiving_item_delivered_received=C2=A0 (cost=3D0.00..28= 777.82 rows=3D352006 width=3D39) (actual time=3D0.011..184.677 rows=3D35335= 1 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 Filter: (((COALESCE(serial_no, ''::character varying))::text <> = ''::text) AND ((eventtype)::text =3D 'LineItemdetailsReceived'::text) AND (= (replenishmenttype)::text =3D 'DC2SWARRANTY'::text))""=C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 55= 953""Planning Time: 0.197 ms""Execution Time: 3466.985 ms" Table DDL:=C2=A0 CREATE TABLE receiving_item_delivered_received(=C2=A0 =C2=A0 load_dttm time= stamp with time zone,=C2=A0 =C2=A0 iamuniqueid character varying(200)=C2=A0= ,=C2=A0 =C2=A0 batchid character varying(200)=C2=A0 ,=C2=A0 =C2=A0 eventid= character varying(200)=C2=A0 ,=C2=A0 =C2=A0 eventtype character varying(20= 0)=C2=A0 ,=C2=A0 =C2=A0 eventversion character varying(200)=C2=A0 ,=C2=A0 = =C2=A0 eventtime timestamp with time zone,=C2=A0 =C2=A0 eventproducerid cha= racter varying(200)=C2=A0 ,=C2=A0 =C2=A0 deliverynumber character varying(2= 00)=C2=A0 ,=C2=A0 =C2=A0 activityid character varying(200)=C2=A0 ,=C2=A0 = =C2=A0 applicationid character varying(200)=C2=A0 ,=C2=A0 =C2=A0 channelid = character varying(200)=C2=A0 ,=C2=A0 =C2=A0 interactionid character varying= (200)=C2=A0 ,=C2=A0 =C2=A0 sessionid character varying(200)=C2=A0 ,=C2=A0 = =C2=A0 receivingplant character varying(200)=C2=A0 ,=C2=A0 =C2=A0 deliveryd= ate date,=C2=A0 =C2=A0 shipmentdate date,=C2=A0 =C2=A0 shippingpoint charac= ter varying(200)=C2=A0 ,=C2=A0 =C2=A0 replenishmenttype character varying(2= 00)=C2=A0 ,=C2=A0 =C2=A0 numberofpackages character varying(200)=C2=A0 ,=C2= =A0 =C2=A0 carrier_id character varying(200)=C2=A0 ,=C2=A0 =C2=A0 carrier_n= ame character varying(200)=C2=A0 ,=C2=A0 =C2=A0 billoflading character vary= ing(200)=C2=A0 ,=C2=A0 =C2=A0 pro_no character varying(200)=C2=A0 ,=C2=A0 = =C2=A0 partner_id character varying(200)=C2=A0 ,=C2=A0 =C2=A0 deliveryitem = character varying(200)=C2=A0 ,=C2=A0 =C2=A0 ponumber character varying(200)= =C2=A0 ,=C2=A0 =C2=A0 poitem character varying(200)=C2=A0 ,=C2=A0 =C2=A0 tr= acking_no character varying(200)=C2=A0 ,=C2=A0 =C2=A0 serial_no character v= arying(200)=C2=A0 ,=C2=A0 =C2=A0 sto_no character varying(200)=C2=A0 ,=C2= =A0 =C2=A0 sim_no character varying(200)=C2=A0 ,=C2=A0 =C2=A0 sku character= varying(200)=C2=A0 ,=C2=A0 =C2=A0 quantity numeric(15,2),=C2=A0 =C2=A0 uom= character varying(200)=C2=A0=C2=A0); -- Index: receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx -- DROP INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_i= dx; CREATE INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_id= x=C2=A0 =C2=A0 ON receiving_item_delivered_received USING btree=C2=A0 =C2= =A0 (eventtype=C2=A0 , replenishmenttype=C2=A0 , COALESCE(serial_no, ''::ch= aracter varying)=C2=A0 )=C2=A0 =C2=A0 ;-- Index: receiving_item_delivered_r= ece_serial_no_eventtype_replenish_idx -- DROP INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_i= dx; CREATE INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_id= x=C2=A0 =C2=A0 ON receiving_item_delivered_received USING btree=C2=A0 =C2= =A0 (serial_no=C2=A0 , eventtype=C2=A0 , replenishmenttype=C2=A0 )=C2=A0 = =C2=A0=C2=A0=C2=A0 =C2=A0 WHERE eventtype::text =3D 'LineItemdetailsReceive= d'::text AND replenishmenttype::text =3D 'DC2SWARRANTY'::text AND COALESCE(= serial_no, ''::character varying)::text <> ''::text;-- Index: receiving_ite= m_delivered_recei_eventtype_replenishmenttype_idx1 -- DROP INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_id= x1; CREATE INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx= 1=C2=A0 =C2=A0 ON receiving_item_delivered_received USING btree=C2=A0 =C2= =A0 (eventtype=C2=A0 , replenishmenttype=C2=A0 )=C2=A0 =C2=A0=C2=A0=C2=A0 = =C2=A0 WHERE eventtype::text =3D 'LineItemdetailsReceived'::text AND replen= ishmenttype::text =3D 'DC2SWARRANTY'::text;-- Index: receiving_item_deliver= ed_receiv_eventtype_replenishmenttype_idx -- DROP INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_i= dx; CREATE INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_id= x=C2=A0 =C2=A0 ON receiving_item_delivered_received USING btree=C2=A0 =C2= =A0 (eventtype=C2=A0 , replenishmenttype=C2=A0 )=C2=A0 =C2=A0 ;-- Index: re= ceiving_item_delivered_received_eventtype_idx -- DROP INDEX receiving_item_delivered_received_eventtype_idx; CREATE INDEX receiving_item_delivered_received_eventtype_idx=C2=A0 =C2=A0 O= N receiving_item_delivered_received USING btree=C2=A0 =C2=A0 (eventtype=C2= =A0 )=C2=A0 =C2=A0 ;-- Index: receiving_item_delivered_received_replenishme= nttype_idx -- DROP INDEX receiving_item_delivered_received_replenishmenttype_idx; CREATE INDEX receiving_item_delivered_received_replenishmenttype_idx=C2=A0 = =C2=A0 ON receiving_item_delivered_received USING btree=C2=A0 =C2=A0 (reple= nishmenttype=C2=A0 )=C2=A0 =C2=A0 ; Thanks,Rj =20 ------=_Part_3392648_326413305.1599254645136 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
query planner:




=20
=20
On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj = Raj <nagaraj.sf@yahoo.com> wrote:


=
I have a query which will more often run on DB and very slow and it is= doing 'seqscan'. I was trying to optimize it by adding indexes in differen= t ways but nothing helps.

Any suggestions?


Query:

EXPALIN ANALYZE select serial_no,receivingplant,sku,r3_eventtime 
from (select serial_no,receivingplant,sku,eventtime as r3_eventtime= , row_number() over (partition by serial_no order by eventtime desc) as mpo= s 
from receiving_item_delivered_received 
wh= ere eventtype=3D'LineItemdetailsReceived'
and replenishmenttype = =3D 'DC2SWARRANTY'
and coalesce(serial_no,'') <> ''
) Rec where mpos =3D 1;


Query Pl= anner: 

"Subquery Scan on rec  (cost=3D7= 0835.30..82275.49 rows=3D1760 width=3D39) (actual time=3D2322.999..3451.783= rows=3D333451 loops=3D1)"
"  Filter: (rec.mpos =3D 1)"
"  Rows Removed by Filter: 19900"
"  ->  = WindowAgg  (cost=3D70835.30..77875.42 rows=3D352006 width=3D47) (actua= l time=3D2322.997..3414.384 rows=3D353351 loops=3D1)"
"  &nb= sp;     ->  Sort  (cost=3D70835.30..71715.31 rows=3D= 352006 width=3D39) (actual time=3D2322.983..3190.090 rows=3D353351 loops=3D= 1)"
"              Sort Key: r= eceiving_item_delivered_received.serial_no, receiving_item_delivered_receiv= ed.eventtime DESC"
"            &nb= sp; Sort Method: external merge  Disk: 17424kB"
"  &nbs= p;           ->  Seq Scan on receiving_ite= m_delivered_received  (cost=3D0.00..28777.82 rows=3D352006 width=3D39)= (actual time=3D0.011..184.677 rows=3D353351 loops=3D1)"
"  =                   Filter: (((C= OALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((e= venttype)::text =3D 'LineItemdetailsReceived'::text) AND ((replenishmenttyp= e)::text =3D 'DC2SWARRANTY'::text))"
"       =             Rows Removed by Filter: 55953"
"Planning Time: 0.197 ms"
"Execution Time: 3466.985 ms"<= /div>

Table DDL: 

CREATE T= ABLE receiving_item_delivered_received
(
    = load_dttm timestamp with time zone,
    iamuniqueid cha= racter varying(200)  ,
    batchid character varyi= ng(200)  ,
    eventid character varying(200) = ; ,
    eventtype character varying(200)  ,
<= div>    eventversion character varying(200)  ,
&nb= sp;   eventtime timestamp with time zone,
    even= tproducerid character varying(200)  ,
    delivery= number character varying(200)  ,
    activityid ch= aracter varying(200)  ,
    applicationid characte= r varying(200)  ,
    channelid character varying(= 200)  ,
    interactionid character varying(200)&n= bsp; ,
    sessionid character varying(200)  ,
    receivingplant character varying(200)  ,
    deliverydate date,
    shipmentdate dat= e,
    shippingpoint character varying(200)  ,
    replenishmenttype character varying(200)  ,
=
    numberofpackages character varying(200)  ,
    carrier_id character varying(200)  ,
  =   carrier_name character varying(200)  ,
    = billoflading character varying(200)  ,
    pro_no = character varying(200)  ,
    partner_id character= varying(200)  ,
    deliveryitem character varyin= g(200)  ,
    ponumber character varying(200) = ; ,
    poitem character varying(200)  ,
    tracking_no character varying(200)  ,
  =   serial_no character varying(200)  ,
    sto= _no character varying(200)  ,
    sim_no character= varying(200)  ,
    sku character varying(200)&nb= sp; ,
    quantity numeric(15,2),
  &nbs= p; uom character varying(200)  
);

=

-- Index: receiving_item_delivered_rece_eventtype_reple= nishmenttype_c_idx

-- DROP INDEX receiving_item_de= livered_rece_eventtype_replenishmenttype_c_idx;

CR= EATE INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx<= /div>
    ON receiving_item_delivered_received USING btree
    (eventtype  , replenishmenttype  , COALESCE= (serial_no, ''::character varying)  )
    ;
<= div>-- Index: receiving_item_delivered_rece_serial_no_eventtype_replenish_i= dx

-- DROP INDEX receiving_item_delivered_rece_ser= ial_no_eventtype_replenish_idx;

CREATE INDEX recei= ving_item_delivered_rece_serial_no_eventtype_replenish_idx
 =   ON receiving_item_delivered_received USING btree
  &= nbsp; (serial_no  , eventtype  , replenishmenttype  )
<= div>    
    WHERE eventtype::text =3D '= LineItemdetailsReceived'::text AND replenishmenttype::text =3D 'DC2SWARRANT= Y'::text AND COALESCE(serial_no, ''::character varying)::text <> ''::= text;
-- Index: receiving_item_delivered_recei_eventtype_replenis= hmenttype_idx1

-- DROP INDEX receiving_item_delive= red_recei_eventtype_replenishmenttype_idx1;

CREATE= INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1
    ON receiving_item_delivered_received USING btree
<= div>    (eventtype  , replenishmenttype  )
&n= bsp;   
    WHERE eventtype::text =3D 'LineIt= emdetailsReceived'::text AND replenishmenttype::text =3D 'DC2SWARRANTY'::te= xt;
-- Index: receiving_item_delivered_receiv_eventtype_replenish= menttype_idx

-- DROP INDEX receiving_item_delivere= d_receiv_eventtype_replenishmenttype_idx;

CREATE I= NDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_idx
<= div>    ON receiving_item_delivered_received USING btree
    (eventtype  , replenishmenttype  )
&nbs= p;   ;
-- Index: receiving_item_delivered_received_eventtype= _idx

-- DROP INDEX receiving_item_delivered_receiv= ed_eventtype_idx;

CREATE INDEX receiving_item_deli= vered_received_eventtype_idx
    ON receiving_item_deli= vered_received USING btree
    (eventtype  )
=
    ;
-- Index: receiving_item_delivered_received_= replenishmenttype_idx

-- DROP INDEX receiving_item= _delivered_received_replenishmenttype_idx;

CREATE = INDEX receiving_item_delivered_received_replenishmenttype_idx
&nb= sp;   ON receiving_item_delivered_received USING btree
 = ;   (replenishmenttype  )
    ;
Thanks,
Rj
=
------=_Part_3392648_326413305.1599254645136--