why does this select hang postgres???

En un mensaje anterior, Jeremy Hansen escribi�:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

It is legal, but has another semantics. It doesn't mean union, but
product. You will be get every combination of env_info, summary and
plat_info records.

Regards.

Fernando P. Schapachnik
Planificaci�n de red y tecnolog�a
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381

On Fri, 10 Aug 2001, Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

Well, without a JOIN or a WHERE clause, this query is going to try create
the raw cartesian product of the two tables. If you have a lot of data in
your tables, this might take a while, because it will return every
possible combination of rows.

Regards,
Jw
--
jlx@commandprompt.com
by way of pgsql-general@commandprompt.com

On Fri, 10 Aug 2001 pgsql-general@commandprompt.com wrote:

On Fri, 10 Aug 2001, Jeremy Hansen wrote:

select * from env_info,summary,plat_info;
Is my statement broken? Is this not legal? Postgres just sits there....

Well, without a JOIN or a WHERE clause, this query is going to try create
the raw cartesian product of the two tables.

Er, thought that was a dot. Make that three tables, even worse if these
are even large-ish tables.

I have no idea what your table structure is like, but assuming there is
some sort of relation between these tables, you'd really want to do
something like:

SELECT * FROM env_info INNER JOIN summary
ON (env_info.id=summary.id) INNER JOIN plat_info
ON (env_info.id=plat_info.id);

If there's no relationship between these tables, alternatively, I'm not
sure what you're trying to accomplish. ;)

Regards,
Jw.
--
jlx@commandprompt.com by way of pgsql-general@commandprompt.com

Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

Its quite possibly broken. It's legal to PostgreSQL; other databases
won't like the missing FROM clause. But PostgreSQL will generate the
Cartesian product of all three tables. If table 1 has 100 rows,
table 2 has 100 rows, and table 3 has 100 rows, PostgreSQL will have
to construct and return 100 * 100 * 100 rows = 1,000,000 rows.

My goal is to select everything from multiple tables with one sql
statement.

Are you sure you want each row of each table combined with each row
of each of the other tables? If these tables are related in any way
you have to provide a WHERE clause that specifies the relationship.

Thank You!
-jeremy

Mike Mascari
mascarm@mascari.com

On Fri, 10 Aug 2001, Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

I don't think it's doing what you want. You've asked for the
join of all three tables, ie, each row in env_info crossed
with each row of summary crossed with each row of plat_info
(so if say each table had 10^4 rows, you'd get like 10^12
rows out).

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits

there....

My goal is to select everything from multiple tables with one sql
statement.

Without making comment on the legality of the SQL statement, simply
selecting everything from the tables is almost never a good idea, and most
tasks can almost always be done in a better manner. Usually when I see
people doing that, they are doing the equivalent of an SQL join in their
program - and usually vastly less efficiently than the database would be
able to do it. Other times, they're selecting everything, then throwing
away what they don't need, again, usually not as efficiently as the
database could do it, not to mention clobbering the system bus....

steve

Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

Is it just sitting there or are you making Pg work really hard? You realize
that you are forcing a product combination of those three tables...if
there's 100 rows in each table, your result set is going to include
100*100*100 or 1,000,000 rows. So if those tables are of even slightly
significant size, your result set is going to be ridiculous.

I'm guessing select * from three tables is not what you wanted, anyhow, but
without specifics....

-Fran

On Fri, 10 Aug 2001, Mike Mascari wrote:

Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

Its quite possibly broken. It's legal to PostgreSQL; other databases
won't like the missing FROM clause. But PostgreSQL will generate the
Cartesian product of all three tables. If table 1 has 100 rows,
table 2 has 100 rows, and table 3 has 100 rows, PostgreSQL will have
to construct and return 100 * 100 * 100 rows = 1,000,000 rows.

My goal is to select everything from multiple tables with one sql
statement.

Are you sure you want each row of each table combined with each row
of each of the other tables? If these tables are related in any way
you have to provide a WHERE clause that specifies the relationship.

Ok, I limited this using a where clause and it's still killing postgres:

select * from summary,env_info,plat_info where summary.bug_id=81;

thanks
-jeremy

Thank You!
-jeremy

Mike Mascari
mascarm@mascari.com

--
salad.

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

You didn't join the tables. This will join each row to every other row.
This will take a long time.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Jeremy Hansen wrote:

On Fri, 10 Aug 2001, Mike Mascari wrote:

Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

Its quite possibly broken. It's legal to PostgreSQL; other databases
won't like the missing FROM clause. But PostgreSQL will generate the
Cartesian product of all three tables. If table 1 has 100 rows,
table 2 has 100 rows, and table 3 has 100 rows, PostgreSQL will have
to construct and return 100 * 100 * 100 rows = 1,000,000 rows.

My goal is to select everything from multiple tables with one sql
statement.

Are you sure you want each row of each table combined with each row
of each of the other tables? If these tables are related in any way
you have to provide a WHERE clause that specifies the relationship.

Ok, I limited this using a where clause and it's still killing postgres:

select * from summary,env_info,plat_info where summary.bug_id=81;

I'm going to refer you to Bruce Momjian's book. Here's the chapter
on "Joining Tables":

http://www.ca.postgresql.org/docs/aw_pgsql_book/node55.html

I'm guessing you need to further qualify your query. Again, if table
1 has 100 rows and table 2 has 100 rows and table 3 has 10 rows
matching summary.bug_id = 81, then you'll still get 100,000 rows
returned. Say I want the an employees' name, salary, and any time
they took vacation for *all* employees. I would write the query
like:

SELECT * FROM employees, salaries, vacations
WHERE employees.employeeid = salaries.employeid AND
employees.employeeid = vacations.employeeid;

If I wanted to qualify it further, say to fetch all the above
information for employee #32, I'd write:

SELECT * FROM employees, salaries, vacations
WHERE employees.employeeid = salaries.employeeid AND
employees.employeeid = vacations.employeeid AND
employees.employeeid = 32;

Please read Bruce's book as it will solve all your problems and
answer all your questions.

Mike Mascari
mascarm@mascari.com

On Fri, 10 Aug 2001, Bruce Momjian wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

You didn't join the tables. This will join each row to every other row.
This will take a long time.

Any tips on this syntax? I'll go look at joins right now I guess :-)

-jeremy

--
salad.

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

Thank You!
-jeremy

Well, you'll get everything and then some ;)

This statement is called a cartesian join. What that means you will get the
<# of rows in env_info> *times* <# of rows in summary> *times* <# of rows in
plat_info>. So if env_info has 1000 rows,summary has 5000 rows, and
plat_info has 200 rows, the query will try to return 1000 * 5000 * 200 =
1,000,000,000 rows! So while it may *seem* to just sit there, Postgres is
actually probably just working *really* hard.

HTH,

Joe

On Fri, 10 Aug 2001, Bruce Momjian wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

You didn't join the tables. This will join each row to every other row.
This will take a long time.

Any tips on this syntax? I'll go look at joins right now I guess :-)

See chapter on joins:

http://www.postgresql.org/docs/awbook.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

How do you know it is just sitting there. Do you see that cpu isn't
being used? How many rows do you expect this to return? What version of
postgres? (do a select version())

Jeremy Hansen wrote:

select * from env_info,summary,plat_info;

Is my statement broken? Is this not legal? Postgres just sits there....

My goal is to select everything from multiple tables with one sql
statement.

Thank You!
-jeremy

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com