Union Query Improvement

Ray Bannon wrote:

I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION

Ad infinitum for about 100 iterations.

I'm sure I'm missing something but wouldn't this give the same results:

select id, plan_name from table where rownum=1 and plan_name in ('A',
'B', 'C');

?

Or do the rownum's change later on?

--
Postgresql & php tutorials
http://www.designmagick.com/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/13/07 07:46, Ray Bannon wrote:

I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION

Ad infinitum for about 100 iterations.

Any way to write this more efficiently?

Just out of curiosity: why does your(?) design have 100 tables/views
with the same (or almost identical) structure?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0rpTS9HxQb37XmcRAtH5AJ4zz5NPM5rBsNWLrKC+/Md6GhxCNgCfZBHf
AeMbTRNKp4guK81pGwfU5wc=
=t9y+
-----END PGP SIGNATURE-----

Ray Bannon <bannonr@comcast.net> writes:

Any way to write this more efficiently?

UNION -> UNION ALL, perhaps? Do you really need UNION's duplicate-row-
elimination behavior?

regards, tom lane

There is only one table/view, it's getting one record for each of the
hundred or so plan ID's that I'm looking for.

On 2/13/07 11:29 PM, in article 45D2BA53.2010100@cox.net, "Ron Johnson"
<ron.l.johnson@cox.net> wrote:

On Tue, 2007-02-13 at 05:46 -0800, Ray Bannon wrote:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION

...

Ad infinitum for about 100 iterations.

Any way to write this more efficiently?

I assume that "table/view" in your OP really refers to different tables
or views; otherwise, this is a trivial query that can be collapsed to
"select ... where plan_name in ('A','B','C') ..." or, perhaps just
"select ... where rownum=1".

As Tom said, UNION ALL may be more appropriate for your needs. See
http://www.postgresql.org/docs/8.1/interactive/queries-union.html

You should consider inherited tables and table partitioning. See
http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html

Partitioning offers two administrative benefits: consistency of child
tables and an implied UNION ALL of children when one selects on the
parent.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Ever considered using a materialized view? If you do, you could write
the code to load/sync your materialized view using a FOR loop. Note that
PostgreSQL does not support materialized views out of the box, so you'd
need to play around with some triggers and functions. If you're
interested, this is a good starting point:

http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html

If you don't change the involved tables all that much, you'll incur in
very little overhead for maintaining the materialized view. If you do
change them quite a bit, you might wanna take a step back and reconsider
parts of your schema design.

BTW, are all your "table/view" the same for each of the SELECTs? I'm
assuming not...

Demian

Ray Bannon wrote:

There is only one table/view, it's getting one record for each of the
hundred or so plan ID's that I'm looking for.

On 2/13/07 11:29 PM, in article 45D2BA53.2010100@cox.net, "Ron Johnson"
<ron.l.johnson@cox.net> wrote:

On 02/13/07 07:46, Ray Bannon wrote:

I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION

Ad infinitum for about 100 iterations.

Any way to write this more efficiently?

Just out of curiosity: why does your(?) design have 100 tables/views
with the same (or almost identical) structure?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFF1KJyWs7G5iIp9akRArwOAKCSZTDyfxArgLHJSOozmbopALtfaACfURB0
m4s1oSyNqkQjYcx4//AcfTA=
=l7AK
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 06:43, Ray Bannon wrote:

There is only one table/view, it's getting one record for each of the

Clarity in question-asking always helps.

hundred or so plan ID's that I'm looking for.

One table, huh? Then why all the references to "ROWNUM = 1"? To us
who don't know your system, ROWNUM looks a whole lot like a PK.

Anyway...

SELECT ID, PLAN_NAME
FROM SOME_TABLE
WHERE PLAN_NAME IN ('A', 'B', 'C', etc, etc)
AND ROWNUM = 1l

Making a lookup table with just a list of PLAN_NAME values in it
would also work, but then you'd have to join the lookup table to
SOME_TABLE on PLAN_NAME.

On 2/13/07 11:29 PM, in article 45D2BA53.2010100@cox.net, "Ron Johnson"
<ron.l.johnson@cox.net> wrote:

On 02/13/07 07:46, Ray Bannon wrote:

I have a query which is running a bit slowly, and I'm wondering if anyone
has a design improvement. Basically it's a series of unions as follows:

Select ID, plan_name from table/view
Where plan_name = 'A'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'B'
And rownum = 1
UNION
Select ID, plan_name from table/view
Where plan_name = 'C'
And rownum = 1
UNION

Ad infinitum for about 100 iterations.

Any way to write this more efficiently?

Just out of curiosity: why does your(?) design have 100 tables/views
with the same (or almost identical) structure?

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF1KJ4S9HxQb37XmcRAs+AAKCad0JDoZgwXXpxXHNvXm/8BG1QRgCeLW+H
bAFQXlbtYfq27z4WDjsKahY=
=4JYC
-----END PGP SIGNATURE-----