Query performance issue

From: Nagaraj Raj <nagaraj(dot)sf(at)yahoo(dot)com>
To: Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Query performance issue
Date: 2020-09-04 21:18:41
Message-ID: 975305787.3395625.1599254321500@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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 different 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 mpos from receiving_item_delivered_received where eventtype='LineItemdetailsReceived'and replenishmenttype = 'DC2SWARRANTY'and coalesce(serial_no,'') <> '') Rec where mpos = 1;

Query Planner: 
"Subquery Scan on rec  (cost=70835.30..82275.49 rows=1760 width=39) (actual time=2322.999..3451.783 rows=333451 loops=1)""  Filter: (rec.mpos = 1)""  Rows Removed by Filter: 19900""  ->  WindowAgg  (cost=70835.30..77875.42 rows=352006 width=47) (actual time=2322.997..3414.384 rows=353351 loops=1)""        ->  Sort  (cost=70835.30..71715.31 rows=352006 width=39) (actual time=2322.983..3190.090 rows=353351 loops=1)""              Sort Key: receiving_item_delivered_received.serial_no, receiving_item_delivered_received.eventtime DESC""              Sort Method: external merge  Disk: 17424kB""              ->  Seq Scan on receiving_item_delivered_received  (cost=0.00..28777.82 rows=352006 width=39) (actual time=0.011..184.677 rows=353351 loops=1)""                    Filter: (((COALESCE(serial_no, ''::character varying))::text <> ''::text) AND ((eventtype)::text = 'LineItemdetailsReceived'::text) AND ((replenishmenttype)::text = 'DC2SWARRANTY'::text))""                    Rows 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)  ,    batchid character varying(200)  ,    eventid character varying(200)  ,    eventtype character varying(200)  ,    eventversion character varying(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)  ,    interactionid character varying(200)  ,    sessionid character varying(200)  ,    receivingplant character varying(200)  ,    deliverydate date,    shipmentdate date,    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 varying(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)  ,    quantity numeric(15,2),    uom character varying(200)  );

-- Index: receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx
-- DROP INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx;
CREATE INDEX receiving_item_delivered_rece_eventtype_replenishmenttype_c_idx    ON receiving_item_delivered_received USING btree    (eventtype  , replenishmenttype  , COALESCE(serial_no, ''::character varying)  )    ;-- Index: receiving_item_delivered_rece_serial_no_eventtype_replenish_idx
-- DROP INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_idx;
CREATE INDEX receiving_item_delivered_rece_serial_no_eventtype_replenish_idx    ON receiving_item_delivered_received USING btree    (serial_no  , eventtype  , replenishmenttype  )        WHERE eventtype::text = 'LineItemdetailsReceived'::text AND replenishmenttype::text = 'DC2SWARRANTY'::text AND COALESCE(serial_no, ''::character varying)::text <> ''::text;-- Index: receiving_item_delivered_recei_eventtype_replenishmenttype_idx1
-- DROP INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1;
CREATE INDEX receiving_item_delivered_recei_eventtype_replenishmenttype_idx1    ON receiving_item_delivered_received USING btree    (eventtype  , replenishmenttype  )        WHERE eventtype::text = 'LineItemdetailsReceived'::text AND replenishmenttype::text = 'DC2SWARRANTY'::text;-- Index: receiving_item_delivered_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  , replenishmenttype  )    ;-- Index: receiving_item_delivered_received_eventtype_idx
-- DROP INDEX receiving_item_delivered_received_eventtype_idx;
CREATE INDEX receiving_item_delivered_received_eventtype_idx    ON receiving_item_delivered_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    ON receiving_item_delivered_received USING btree    (replenishmenttype  )    ;
Thanks,Rj

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Thomas Kellerer 2020-09-04 21:23:38 Re: Query performance issue
Previous Message Michael Lewis 2020-08-26 13:32:41 Re: Too few rows expected by Planner on partitioned tables