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 )
;
Rj
