Hello.
Allow me to apologize in advance regarding any abnormal phrasing.
There is a certain table, hereby dubbed IPT, containing 4 keys and a variety of values.
I wish to select several values spread throughout IPT, anchored via the first three keys and a varying fourth.
Currently, this is achieved by individually inner / left joining each required instance of key_4.
Problem is, a test case with 30 such instances takes a minimum of 10 seconds, despite a low explain cost (1 per nested loop). The real query has 40 tables, about the same cost as in the technique described below, yet 25 seconds' minimum runtime (compared to ~1.5s).
I know of one workaround, which looks like this:
select t1.key_1,
t1.key_2,
t1.key_3,
max(case when t2.key_4 = 1 then value_4 end) v_4_1,
max(case when t2.key_4 = 2 then value_3 end) v_3_2,
(...)
from ipt t1
inner join ipt t2
on t2.key_1 = t1.key_1
and t2.key_2 = t1.key_2
and t2.key_3 = t1.key_3
and t2.key_4 in (1,2, ...)
where t1.key_1 (...)
group by t1.key_1, t1.key_2, t1.key_3
However, this technique is fairly limited - max means you won't get more than one row,
cannot impose conditions amongst the combined tables, must adjust everything else to fit the group.
Tl;dr What would make a faster alternative to joining a large quantity of tables?
Thanks.