Thread: why does this select hang postgres???
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 -- salad.
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
> > 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: > > 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
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
> > 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, 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.
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
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
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.
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, 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
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