pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedUnion out performs the single statement
2+ messages / 1 participants
[nested] [flat]
* Union out performs the single statement
@ 2011-06-23 15:44 John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 2+ messages in thread
From: John Fabiani @ 2011-06-23 15:44 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Union out performs the single statement
@ 2011-06-23 16:45 John Fabiani <johnf@jfcomputer.com>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 0 replies; 2+ messages in thread
From: John Fabiani @ 2011-06-23 16:45 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-06-23 16:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-06-23 15:44 Union out performs the single statement John Fabiani <johnf@jfcomputer.com>
2011-06-23 16:45 ` John Fabiani <johnf@jfcomputer.com>
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