Hello,
I found strange thing.
when I execute query with join, I receive result after 58 seconds, if I don’t use joins execution time is 2 sec.
select a11.field_1_id field_1_id,
max(a12.field_1_name) field_1_name,
a11.field_2_id field_2_id,
max(a13.field_2_name) field_2_name,
a11.field_3_id field_3_id,
max(a14.field_3_name) field_3_name,
a11.job_id job_id,
a11.loan_id loan_id,
a11.field_4_id field_4_id,
max(a15.field_4_name) field_4_name,
sum(a11.column1) WJXBFS1,
sum(a11.column2) WJXBFS2,
sum(a11.column3) WJXBFS3,
sum(a11.column4) WJXBFS4,
sum(a11.column5) WJXBFS5
from my_table a11
join field_1 a12
on (a11.field_1_id = a12.field_1_id)
join field_2 a13
on (a11.field_2_id = a13.field_2_id)
join field_3 a14
on (a11.field_3_id = a14.field_3_id)
join field_4 a15
on (a11.field_4_id = a15.field_4_id)
where (a11.job_id = 1
or a11.job_id = 2)
group by a11.field_1_id,
a11.field_2_id,
a11.field_3_id,
a11.job_id,
a11.loan_id,
a11.field_4_id;
——————- 58 seconds—————-
select a11.field_1_id field_1_id,
a11.field_2_id field_2_id,
a11.field_3_id field_3_id,
a11.job_id job_id,
a11.loan_id loan_id,
a11.field_4_id field_4_id,
sum(a11.column1) WJXBFS1,
sum(a11.column2) WJXBFS2,
sum(a11.column3) WJXBFS3,
sum(a11.column4) WJXBFS4,
sum(a11.column5) WJXBFS5
from my_table a11
where (a11.job_id = 1
or a11.job_id = 2)
group by a11.field_1_id,
a11.field_2_id,
a11.field_3_id,
a11.job_id,
a11.loan_id,
a11.field_4_id;
———2 seconds———
In both cases result set is 300 K records.
Could you please explain why first query took long time execution?
Thanks,
Alexander.
