You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by breathem <va...@gmail.com> on 2020/02/21 11:45:49 UTC
Long running query
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/
Re: Long running query
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <va...@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/
>
Re: Long running query
Posted by breathem <va...@gmail.com>.
Yes, thats exactly what we need.
Thanx.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Long running query
Posted by Vladimir Pligin <vo...@yandex.ru>.
Hi,
The most useful information here is the plan that the RDBMS uses.
Is it possible to share it?
I suppose that using of either the IDX_ORG_MEDP_DRUG index or the
IDX_DRUG_ID index is not absolutely correct.
My bet is an index on (DRUG_ID, ORG_ID) could help here but it's to be
checked.
And it's important to clarify whether you really need a left join here.
Inner join would help to use separate "where" clause to have different
indexes for the join and for the filtering.
What do you think, does it make any sense here?
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Long running query
Posted by Denis Magda <dm...@apache.org>.
Please check you followed all standard recommendations summarized on this
page:
https://www.gridgain.com/docs/latest/perf-troubleshooting-guide/sql-tuning
Pay attention to the "Basic Considerations: GridGain vs RDBMS" section.
-
Denis
On Fri, Feb 21, 2020 at 3:45 AM breathem <va...@gmail.com> wrote:
> 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/
>