From: John Fabiani <johnf@jfcomputer.com>
To: pgsql-sql@postgresql.org
Subject: Union out performs the single statement
Date: Thu, 23 Jun 2011 08:44:49 -0700
Message-ID: <201106230844.49303.johnf@jfcomputer.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: johnf@jfcomputer.com
Subject: Re: Union out performs the single statement
In-Reply-To: <201106230844.49303.johnf@jfcomputer.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox