query planner:SPJe | explain.depesz.com
|
|
| |
SPJe | explain.depesz.com
|
|
|
On Friday, September 4, 2020, 02:19:06 PM PDT, Nagaraj Raj
<[email protected]> wrote:
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
