From: John Fabiani <johnf@jfcomputer.com>
Organization: J.F. Computer Solutions
To: pgsql-sql@postgresql.org
Subject: Union out performs the single statement
Date: Thu, 23 Jun 2011 08:44:49 -0700
User-Agent: KMail/1.13.5 (Linux/2.6.34.8-0.2-desktop; KDE/4.4.4; i686; ; )
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201106230844.49303.johnf@jfcomputer.com>

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