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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
