Query performance issue

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux