0

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
9
  • Why, is that too slow for you? Dec 2, 2022 at 12:50
  • ---------- DATABASE OPERATIONS ---------- TOTAL AVERAGE LONGEST SHORTEST COUNT ELEMENT millisec microsec microsec microsec 10,522 701 142,801 589 15,000 Dec 2, 2022 at 12:57
  • Please edit the question and add relevant information there. Perhaps you should post an execution plan for a slow execution of the query, otherwise we cannot analyze it. Dec 2, 2022 at 12:59
  • 0.073 milliseconds seems quite fast to me. How fast do you need that to be? Dec 2, 2022 at 13:21
  • @Laurenz Albe Thanks. I will collect it from team and post. Dec 2, 2022 at 14:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy