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/
