Re: Union out performs the single statement

On Thursday, June 23, 2011 08:44:49 am John Fabiani wrote:
> 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

Thanks to all that took the time to review my SQL.  I in fact solved the issue 
by adding a join and moving the date range to the where.  Thanks for the help.

Johnf