cockroachdb performance

Hi All,

Below sql is taking around 150 seconds to fetch 1000 records. Do we have any way to improve the performance other than indexes.

SELECT
inv.id,
inv.code,
i.odf_object_code,
pr.is_program,
itpl.full_name IT_Portfolio_Lead,
pd.full_name Program_Director,
pm.full_name Program_Manager,
SSD.full_name Senior_Service_Director,
SD.full_name Service_Director,
ITO.full_name IT_Owner,
ITPM.full_name IT_Portfolio_MGR_OCIO,
ERC.full_name Est_Review_Council_Lead,
CC.full_name Capital_Controller,
BO.full_name Business_Owner,
–BPM.full_name Business_Project_Manager,
i.OPT_BUSPRJMGR_t Business_Project_Manager,
CB.full_name Created_By,
LUB.full_name Last_Update_By,
i.opt_it_owner as IT_Owner_ResourceId
, BORIO.full_name as Business_Owner_RIO
, BWM.full_name as Business_Work_Manager
, BPL.full_name as Business_Primary_Lead
, BSL.full_name as Business_Secondary_Lead,
CB.Unique_name as resource_id,
Port_dir.full_name as Portfolio_Director
from
public.ppmo_inv_investments inv
inner join public.ppmo_inv_Projects pr on (inv.id = pr.prid)
inner join public.ppmo_odf_ca_Project odf_pr on (inv.id = odf_pr.id)
inner join (select id,odf_object_code,opt_portfolio,opt_portfolio_lead,opt_prg_dir,opt_prgm_mgr,OPT_SR_SVC_DIR,opt_it_owner,OPT_IT_PORTFOLIO_MGR,
OPT_ERC_LEAD,OPT_CAPCONTROL,OPT_BIZ_OWN,OPT_BUSPRJMGR_t,OPT_SVC_DIR From public.ppmo_odf_ca_inv where odf_object_code = ‘project’) i on (inv.id = i.id)
left outer join public.ppmo_odf_ca_opt_portfolio pf on (i.opt_portfolio = pf.code)
left outer join public.ppmo_srm_resources ITPL on (i.opt_portfolio_lead = ITPL.id)
left outer join public.ppmo_srm_resources PD on (i.opt_prg_dir = PD.id)
left outer join public.ppmo_srm_resources Pm on (i.opt_prgm_mgr = pm.id)
left outer join public.ppmo_srm_resources SSD on (i.OPT_SR_SVC_DIR = SSD.id)
left outer join public.ppmo_srm_resources SD on (i.OPT_SVC_DIR = SD.id)
left outer join public.ppmo_srm_resources ITO on (i.opt_it_owner = ITO.id)
left outer join public.ppmo_srm_resources ITPM on (i.OPT_IT_PORTFOLIO_MGR = ITPM.id)
left outer join public.ppmo_srm_resources ERC on (i.OPT_ERC_LEAD = ERC.id)
left outer join public.ppmo_srm_resources CC on (i.OPT_CAPCONTROL = CC.id)
left outer join public.ppmo_srm_resources BO on (i.OPT_BIZ_OWN = BO.id)
–left outer join public.ppmo_srm_resources BPM on (i.OPT_BUSPRJMGR = BPM.id)
left outer join public.ppmo_srm_resources CB on (inv.created_by = CB.user_id)
left outer join public.ppmo_srm_resources LUB on (inv.last_updated_by = LUB.user_id)
left outer join public.ppmo_srm_resources BORIO on (odf_pr.opt_bus_owner_bus = BORIO.id)
left outer join public.ppmo_srm_resources BWM on (odf_pr.opt_buzprjmgr = BWM.id)
left outer join public.ppmo_srm_resources BPL on (odf_pr.opt_buz_primary_lead = BPL.id)
left outer join public.ppmo_srm_resources BSL on (odf_pr.opt_buz_sec_lead = BSL.id)
left outer join public.ppmo_srm_resources Port_dir on (odf_pr.OPT_IT_PORTFOLIO_DIR = Port_dir.id)
–where i.odf_object_code = ‘project’
limit 1000


I am attaching the explain plan for the same as below -

distributed	true
vectorized	false

render
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_it_portfolio_dir) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_buz_sec_lead) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_buz_primary_lead) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_buzprjmgr) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_bus_owner_bus) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── hash-join
│ type right outer
│ equality (user_id) = (last_updated_by)
├── scan
│ table ppmo_srm_resources@primary
│ spans FULL SCAN
└── limit
│ count 1000
└── hash-join
│ type right outer
│ equality (user_id) = (created_by)
├── scan
│ table ppmo_srm_resources@primary
│ spans FULL SCAN
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_biz_own) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_capcontrol) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_erc_lead) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_it_portfolio_mgr) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_it_owner) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── cross-join
│ type right outer
│ pred opt_svc_dir = id
├── scan
│ table ppmo_srm_resources@srm_resources_n1
│ spans FULL SCAN
└── limit
│ count 1000
└── cross-join
│ type right outer
│ pred opt_sr_svc_dir = id
├── scan
│ table ppmo_srm_resources@srm_resources_n1
│ spans FULL SCAN
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_prgm_mgr) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_prg_dir) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── lookup-join
│ table ppmo_srm_resources@srm_resources_n1
│ type left outer
│ equality (opt_portfolio_lead) = (id)
│ equality cols are key
│ parallel
└── limit
│ count 1000
└── hash-join
│ type left outer
│ equality (opt_portfolio) = (code)
│ right cols are key
├── limit
│ │ count 1000
│ └── lookup-join
│ │ table ppmo_odf_ca_inv@primary
│ │ type inner
│ │ equality (id) = (id)
│ │ equality cols are key
│ │ parallel
│ │ pred @14 = ‘project’
│ └── lookup-join
│ │ table ppmo_odf_ca_project@primary
│ │ type inner
│ │ equality (id) = (id)
│ │ equality cols are key
│ │ parallel
│ └── lookup-join
│ │ table ppmo_inv_investments@primary
│ │ type inner
│ │ equality (prid) = (id)
│ │ equality cols are key
│ │ parallel
│ └── scan
│ table ppmo_inv_projects@primary
│ spans FULL SCAN
└── scan
table ppmo_odf_ca_opt_portfolio@odf_ca_opt_portfolio_u1
spans FULL SCAN

If you are doing joins you need indexes. Any time you see FULL SCAN that is going to be slow for anything but the smallest of tables.

Thanks Jerry. But, we do have created all the required indexes based on where clause, join conditions. Also, our table sizes are quite huge like ~5 million rows.