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 1kEJ6p-00034o-3E for pgsql-performance@arkaria.postgresql.org; Fri, 04 Sep 2020 21:18:59 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEJ6m-0008UV-IP for pgsql-performance@arkaria.postgresql.org; Fri, 04 Sep 2020 21:18:56 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEJ6l-0008QB-II for pgsql-performance@lists.postgresql.org; Fri, 04 Sep 2020 21:18:56 +0000 Received: from sonic314-21.consmr.mail.ne1.yahoo.com ([66.163.189.147]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kEJ6j-0007iX-1C for pgsql-performance@lists.postgresql.org; Fri, 04 Sep 2020 21:18:54 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1599254330; bh=Jip/OTf2RwY2AGtzdi+jzmdSrHd8jpevGAYKVisXZO4=; h=Date:From:To:Subject:References:From:Subject; b=tDJ0kz/Hyz7BRvvm9lX+8ix9eitPZkKTIX7Qe8F0LHSIURaU9WzRqtyNQ2tMhbQ6Q1EYkaz5fWvJK10oyLZMIPabx0Bu/ZUpn7gm3BViONY0FWhm6tCEnvB8CyPSrzWsblDxWyBO6cv0PEdjaIOWWWf7vZ9z/urYdaXmBCrqskqywfdm5xjPsZ/gl0UStAyFvBgZ/lu/gi8WkeoHXyIKA+jBmiW78pb0itJnEqzHRGtywDHx3xepIH9QrtaAdA/I2Ldqq7jKlfdHqRu4u/01wvsYqrjXYwscoSjnO0S0WMYsrnuECyAK78oXIeNu7hIqledMq5UCQ5yU6McYdEk8eg== X-YMail-OSG: lXf43AQVM1lIfDWP8nEXFQhyt66.pX5e3A5XvTZx.LOu8R2nkAkrO7YKeFeExvN WREi_dAyqvYrF2e.ixmk92_ZmVDHMkPTlO1kqvp45FX.Rh.fAGDWVdBPgiFj36gusWiZS3N7DZpN HTMT2Whzsgql33qFMgrxY8sZR0fCAfWaHwqSja5EsUGWCBrr0j5.hk.0h7dgQdIukX6J47_c9GU9 DZ7ve.i11h06leH5X_w6CP4DgqjowxrAKh8uOkUAScPFyRbDvUBR0vynWOucyZgq_KQzDXGit57s wEMeKQKFFOomzGzOTJt7IH33_DTk7k4z5_rZSOjmQY_ZUTDwX2cK29TRrfMQeRI_VLBdrWh14KCr coP3phluCrqEu5fIA5gcYA4JOXg4KdvfBnENBBEEhurd5RpnEzp_ekMvG_qWSXpauoojTiakiVHw cvDt_k.AxSbSCAfFfqgPKBrTea5LAVgbmXwxuSjrT1pkE_DKFyZAlvEGDPqWavRnj.z9D6QKHa4h 21M9xwtQettSFy96S4QOl5VkgCTmQJCMpFWf1ypLURHVy_rkzOb7.lhITrlysttcIyRU3wU4ht8F bCiAeQzK9j3g2HvO0G1fgBbnIMwCClKL5HOIfDp_l5SqgrgKus..hUP6VioNdVKJjW3E.EVPVxfn pDPhsWyD1rSvl0fDADr_rNfgS3rHhZO8hLjRHmzLrpAy32NIPr5ac3EKgfYdaMm7urXCFY5gbqIi QAK0nQ9rSbTENg_StSZRb_JSykAYX81JsHWYNSo82WLlA8j6kf8kdkKZHwjPt_YwqR8UxOtkrHgA dhslIJPNt_tqf67I7iPAVEwedXkRfkuPXCgjQyU1iyf0iYgA62OQr9dB7bcD1bOy6jszXmhHYL1P JWzfxTuZSmaecwhw7k0oa_uQIRcfQA6mwrftYEnMdTjS30aEjBA1RxhKrTdUjaMSoF3VbcXWEWfb H5Aqn48uJHcSNgaLws465S9bU3smsMlVmeG31m5VJH.zBg5WWNUWHP7yevMZkjHc.RYDm9KbCHwj CmiwnJApXLgrPQgE1i4qIVm9td9I7EKMKMC9Rj5Wx4mYbnNtQ4.q_r6SEj.DeUeNkdtWeTOej_zQ rBHDMyaOTWUf.StJHtL_AWyfoH2oiDHCrqE.fdI4tVqzC1kldIXXeQ5BAiDTmFjvTMmVssDKpfLb OtM15pv5F1uE4XNMbKOuDqtNr1QxIhizfu7aqIIbeKfppoPBh9NmxBpNlAEwpS3m1.Ulkg.GRTPX fkukzUnSlLozaTtYvstk43TGXfM9tI92wDuCz9KqONFzvQYejtTzCgdejICpRkJPNobV6tGJDW0V KAJIYqwbiJR.l_kmgY12UzM6mFAK4R2H6XkYThUBPlsK_xMaY2Ph7HdRCyMtRCyTRG_3BvaFzPhz QUgbB2_eqJJWTedsyUbNKGch6zkHumscJsPa._0Ry Received: from sonic.gate.mail.ne1.yahoo.com by sonic314.consmr.mail.ne1.yahoo.com with HTTP; Fri, 4 Sep 2020 21:18:50 +0000 Date: Fri, 4 Sep 2020 21:18:41 +0000 (UTC) From: Nagaraj Raj To: Pgsql Performance Message-ID: <975305787.3395625.1599254321500@mail.yahoo.com> Subject: Query performance issue MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3395624_390090057.1599254321498" References: <975305787.3395625.1599254321500.ref@mail.yahoo.com> 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: 15326 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_3395624_390090057.1599254321498 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable I have a query which will more often run on DB and very slow and it is doi= ng 'seqscan'. I was trying to optimize it by adding indexes in different wa= ys 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 ------=_Part_3395624_390090057.1599254321498 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
=
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 di= fferent ways but nothing helps.

Any suggestions?


Query:

EXPALIN ANALYZE select serial_no,receivingpl= ant,sku,r3_eventtime 
from (select serial_no,receivingplant,= sku,eventtime as r3_eventtime, row_number() over (partition by serial_no or= der by eventtime desc) as mpos 
from receiving_item_delivere= d_received 
where eventtype=3D'LineItemdetailsReceived'
and replenishmenttype =3D 'DC2SWARRANTY'
and coalesce(seria= l_no,'') <> ''
) Rec where mpos =3D 1;

=

Query Planner: 

"Subquery= Scan on rec  (cost=3D70835.30..82275.49 rows=3D1760 width=3D39) (actu= al time=3D2322.999..3451.783 rows=3D333451 loops=3D1)"
"  Fi= lter: (rec.mpos =3D 1)"
"  Rows Removed by Filter: 19900"
"  ->  WindowAgg  (cost=3D70835.30..77875.42 rows= =3D352006 width=3D47) (actual time=3D2322.997..3414.384 rows=3D353351 loops= =3D1)"
"        ->  Sort  (cost= =3D70835.30..71715.31 rows=3D352006 width=3D39) (actual time=3D2322.983..31= 90.090 rows=3D353351 loops=3D1)"
"        &nb= sp;     Sort Key: receiving_item_delivered_received.serial_no, re= ceiving_item_delivered_received.eventtime DESC"
"    &n= bsp;         Sort Method: external merge  Disk: 17= 424kB"
"              ->&nb= sp; Seq Scan on receiving_item_delivered_received  (cost=3D0.00..28777= .82 rows=3D352006 width=3D39) (actual time=3D0.011..184.677 rows=3D353351 l= oops=3D1)"
"              &nbs= p;     Filter: (((COALESCE(serial_no, ''::character varying))::te= xt <> ''::text) AND ((eventtype)::text =3D 'LineItemdetailsReceived':= :text) AND ((replenishmenttype)::text =3D 'DC2SWARRANTY'::text))"
"                    Row= s Removed by Filter: 55953"
"Planning Time: 0.197 ms"
"= Execution Time: 3466.985 ms"

Table DDL: 

CREATE TABLE receiving_item_delivered_received
(
    load_dttm timestamp with time zone,
=     iamuniqueid character varying(200)  ,
  &= nbsp; batchid character varying(200)  ,
    eventi= d character varying(200)  ,
    eventtype characte= r varying(200)  ,
    eventversion character varyi= ng(200)  ,
    eventtime timestamp with time zone,=
    eventproducerid character varying(200)  ,
    deliverynumber character varying(200)  ,
    activityid character varying(200)  ,
  =   applicationid character varying(200)  ,
   = channelid character varying(200)  ,
    interacti= onid character varying(200)  ,
    sessionid chara= cter varying(200)  ,
    receivingplant character = varying(200)  ,
    deliverydate date,
&= nbsp;   shipmentdate date,
    shippingpoint chara= cter varying(200)  ,
    replenishmenttype charact= er varying(200)  ,
    numberofpackages character = varying(200)  ,
    carrier_id character varying(2= 00)  ,
    carrier_name character varying(200)&nbs= p; ,
    billoflading character varying(200)  ,
    pro_no character varying(200)  ,
 = ;   partner_id character varying(200)  ,
    = deliveryitem character varying(200)  ,
    ponumbe= r character varying(200)  ,
    poitem character v= arying(200)  ,
    tracking_no character varying(2= 00)  ,
    serial_no character varying(200)  = ,
    sto_no character varying(200)  ,
&= nbsp;   sim_no character varying(200)  ,
    = sku character varying(200)  ,
    quantity numeric= (15,2),
    uom character varying(200)  
);


-- Index: receiving_item_d= elivered_rece_eventtype_replenishmenttype_c_idx

--= DROP INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx= ;

CREATE INDEX receiving_item_delivered_rece_event= type_replenishmenttype_c_idx
    ON receiving_item_deli= vered_received USING btree
    (eventtype  , reple= nishmenttype  , COALESCE(serial_no, ''::character varying)  )
    ;
-- Index: receiving_item_delivered_rece_se= rial_no_eventtype_replenish_idx

-- DROP INDEX rece= iving_item_delivered_rece_serial_no_eventtype_replenish_idx;

=
CREATE INDEX receiving_item_delivered_rece_serial_no_eventtype_r= eplenish_idx
    ON receiving_item_delivered_received U= SING btree
    (serial_no  , eventtype  , rep= lenishmenttype  )
    
   = ; WHERE eventtype::text =3D 'LineItemdetailsReceived'::text AND replenishme= nttype::text =3D 'DC2SWARRANTY'::text AND COALESCE(serial_no, ''::character= varying)::text <> ''::text;
-- Index: receiving_item_deliv= ered_recei_eventtype_replenishmenttype_idx1

-- DRO= P INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1;

CREATE INDEX receiving_item_delivered_recei_eventtyp= e_replenishmenttype_idx1
    ON receiving_item_delivere= d_received USING btree
    (eventtype  , replenish= menttype  )
    
    WHER= E eventtype::text =3D 'LineItemdetailsReceived'::text AND replenishmenttype= ::text =3D 'DC2SWARRANTY'::text;
-- Index: receiving_item_deliver= ed_receiv_eventtype_replenishmenttype_idx

-- DROP = INDEX receiving_item_delivered_receiv_eventtype_replenishmenttype_idx;

CREATE INDEX receiving_item_delivered_receiv_eventtype= _replenishmenttype_idx
    ON receiving_item_delivered_= received USING btree
    (eventtype  , replenishme= nttype  )
    ;
-- Index: receiving_item= _delivered_received_eventtype_idx

-- DROP INDEX re= ceiving_item_delivered_received_eventtype_idx;

CRE= ATE INDEX receiving_item_delivered_received_eventtype_idx
  =   ON receiving_item_delivered_received USING btree
  &n= bsp; (eventtype  )
    ;
-- Index: recei= ving_item_delivered_received_replenishmenttype_idx

-- DROP INDEX receiving_item_delivered_received_replenishmenttype_idx;

CREATE INDEX receiving_item_delivered_received_replen= ishmenttype_idx
    ON receiving_item_delivered_receive= d USING btree
    (replenishmenttype  )
=     ;

Thanks,
Rj
------=_Part_3395624_390090057.1599254321498--