Hi,
I have a SELECT statement that is using the regexp_split_to_table function as
follows:
... and fk_topic in (select regexp_split_to_table(eligible_topics, ',')::int
from escourse)
Normally there are 1 to 3 values in eligible_topics as
46,50,43.
The problem is the performance is terrible and I need a way to improve the
performance. I have discovered that if I separate the values in the
eligible_topics field and create a "union all" the performance is great! The
difference is with regexp_split_to_table function = 4 seconds and using the
union = 151 ms
So the Union looks like;
... fk_topic = 46 ...
union all
... fk_topic = 50 ...
union all
... fk_topic = 43 ...
Of course the problem is creating the unions when I don't know in advance what
the number values are in the eligible_topics field.
The complete SQL is:
select round(miles_between_lat_long(l.latitude::numeric, l.longitude::numeric,
c.latitude::numeric, c.longitude::numeric),0) as miles,s.began, s.ended,
s.pkid as sessionid,s.stop_close, l.facility, (select count(*) from esenroll r
where r.sessionid=s.pkid) as enrolled, l.totalseats, (select count(*) from
esclass cl where cl.sessionid=s.pkid and schedule>=current_date) as
classesremaining, tp.ccode from essess s join esloc l on l.pkid = s.locationid
join esclient c on c.pkid = 36757 join agmisc tp on tp.pkid = s.topic where
s.topic in (select regexp_split_to_table(eligible_topics, ',')::int from
escourse) group by 1,2,3,4,5,6,7,8,9,10 having (select count(*) from esclass
cl where cl.sessionid=s.pkid and schedule>= current_date and schedule <=
current_date + 30) > 0 order by 10,1
Without me posting the schema of the database I can see how it would be
difficult to determine the best way to tackle this issue. But I'm hoping
others will see some major issue I have created within the select.
Thanks in advance for any help,
Johnf