Can the below query be improved with new Indexes.Few Index scans are "never executed"
explain analyze select ASSOC.ID,ASSOC.APPLICATIONCONTEXT,ASSOC.ASSOCIATIONTYPE,ASSOC.RELATEDOBJDESCRIPTION,ASSOC.VALIDFROMDATE,ASSOC.VALIDTODATE,ASSOC.CREATEDBY,ASSOC.CREATEDDATE,ASSOC.UPDATEDBY,ASSOC.UPDATEDDATE,ASSOC.STATE,ASSOC.SOURCETYPE,ASSOC.SOURCEID,ASSOC.TARGETTYPE,ASSOC.TARGETID,ASSOC.CUSTOMERID,ASSOC.PRODUCTID,ASSOC.ENTITYSPECIFICATION,ASSOC.DATA,ASSOC.PARTYTYPE,ENTITY.ID,ENTITY.ENTITYTYPE,ENTITY.VALIDFROMDATE,ENTITY.VALIDTODATE,ENTITY.CREATEDBY,ENTITY.CREATEDDATE,ENTITY.UPDATEDBY,ENTITY.UPDATEDDATE,ENTITY.STATE,ENTITY.STATEDATE,ENTITY.CATALOGCODE,ENTITY.CATALOGTYPE,ENTITY.EXTERNALID,ENTITY.REASONCODE,ENTITY.ORDERREF,ENTITY.CORRELATIONID,SER.ID,SER.TYPE,SER.SERVICECANDIDATE,SER.SERVICESPECIFICATION,SER.NAME,SER.ISSERVICEENABLED,SER.HASSTARTED,SER.ISMANDATORY,SER.STARTMODE,SER.ISSTATEFUL,SER.STATUSREASON,SER.VERSION,SER.SELLERORGANIZATION,SER.CUSTOMERORGANIZATION,RES.ID,RES.NAME,RES.ISCOMPOUND,RES.ISLOGICAL,RES.TYPE,RES.RESOURCENUMBER,RES.SERIALNUMBER,RES.SELLERORGANIZATION,RES.CUSTOMERORGANIZATION,CD.ID,CD.DATA
from (select *
from CWT_SR_ASSOCIATION
where SERVICEID = '221010885') ASSOC
left join CWT_SR_ENTITY ENTITY
on ENTITY.ID = ASSOC.TARGETID
left join CWT_SR_SERVICE SER
on SER.ID = ASSOC.TARGETID
left join CWT_SR_RESOURCE RES
on RES.ID = ASSOC.TARGETID
left join CWT_SR_CONTEXTDATA CD
on CD.ID = ASSOC.TARGETID;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=1.38..115.47 rows=7 width=4473) (actual time=0.009..0.010 rows=0 loops=1)
-> Nested Loop Left Join (cost=1.10..87.40 rows=7 width=4272) (actual time=0.008..0.009 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.83..63.34 rows=7 width=3317) (actual time=0.008..0.009 rows=0 loops=1)
-> Nested Loop Left Join (cost=0.56..39.28 rows=7 width=2325) (actual time=0.008..0.008 rows=0 loops=1)
-> Index Scan using ix_sr_assoc_serviceid on cwt_sr_association (cost=0.28..11.21 rows=7 width=1563) (actual time=0.007..0.008 rows=0 loops=1)
Index Cond: ((serviceid)::text = '221010885'::text)
-> Index Scan using pk_cwt_sr_entity on cwt_sr_entity entity (cost=0.28..4.01 rows=1 width=762) (never executed)
Index Cond: ((id)::text = (cwt_sr_association.targetid)::text)
-> Index Scan using pk_cwt_sr_service on cwt_sr_service ser (cost=0.28..3.44 rows=1 width=992) (never executed)
Index Cond: ((id)::text = (cwt_sr_association.targetid)::text)
-> Index Scan using pk_cwt_sr_resource on cwt_sr_resource res (cost=0.28..3.44 rows=1 width=955) (never executed)
Index Cond: ((id)::text = (cwt_sr_association.targetid)::text)
-> Index Scan using pk_cwt_sr_contextdata on cwt_sr_contextdata cd (cost=0.28..4.01 rows=1 width=201) (never executed)
Index Cond: ((id)::text = (cwt_sr_association.targetid)::text)
Planning Time: 0.942 ms
Execution Time: 0.073 ms
(16 rows)
Time: 1.661 ms