Query performance issue

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  )

    ;