Hello! Our current optimizer is not very smart. If you found an USE INDEX which allows your query to run sufficiently fast, my recommendation is to just use it.
Regards, -- Ilya Kasnacheev пт, 21 февр. 2020 г. в 14:45, breathem <vasilylaktio...@gmail.com>: > Hello, > We have two tables LD (8 000 000 rows) and DRUGS (130 000 rows). > Following query is executed ~7 minutes that is significantly longer then in > RDBMS (~1,5 sec): > select d.drug_id, d.drug_name, ld.price > from drugs d > left outer join ld on d.drug_id = ld.drug_id and ld.org_id = 264; > > Explain for query: > SELECT > D__Z0.DRUG_ID AS __C0_0, > D__Z0.DRUG_NAME AS __C0_1, > __Z1.PRICE AS __C0_2 > FROM PUBLIC.DRUGS D__Z0 > /* PUBLIC.IDX_DRUG_ID_NAME */ > LEFT OUTER JOIN PUBLIC.LD __Z1 > /* PUBLIC.IDX_ORG_MEDP_DRUG: ORG_ID = 264 > AND DRUG_ID = D__Z0.DRUG_ID > */ > ON (__Z1.ORG_ID = 264) > AND (D__Z0.DRUG_ID = __Z1.DRUG_ID) > SELECT > __C0_0 AS DRUG_ID, > __C0_1 AS DRUG_NAME, > __C0_2 AS PRICE > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */ > > Indexes on table LD: IDX_ORG_MEDP_DRUGS(ORG_ID, MEDP_ID, DRUG_ID), > IDX_DRUG_ID(DRUG_ID) > Indexes on table DRUGS: IDX_DRUG_ID_NAME(DRUG_ID, DRUG_NAME) > > We try to force IDX_DRUG_ID: > select d.drug_id, d.drug_name, ld.price > from drugs d > left outer join ld use index (idx_drug_id) on d.drug_id = ld.drug_id and > ld.org_id = 264; > > This query is executed 8 sec. > > Explain for query: > SELECT > D__Z0.DRUG_ID AS __C0_0, > D__Z0.DRUG_NAME AS __C0_1, > __Z1.PRICE AS __C0_2 > FROM PUBLIC.DRUGS D__Z0 > /* PUBLIC.IDX_DRUG_ID_NAME */ > LEFT OUTER JOIN PUBLIC.LD __Z1 USE INDEX (IDX_DRUG_ID) > /* PUBLIC.IDX_DRUG_ID: DRUG_ID = D__Z0.DRUG_ID */ > ON (__Z1.ORG_ID = 264) > AND (D__Z0.DRUG_ID = __Z1.DRUG_ID) > SELECT > __C0_0 AS DRUG_ID, > __C0_1 AS DRUG_NAME, > __C0_2 AS PRICE > FROM PUBLIC.__T0 > /* PUBLIC."merge_scan" */ > > How to speed up query? > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ >