pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Optimizations 2+ messages / 2 participants [nested] [flat]
* Optimizations @ 1998-12-03 12:14 Michael Ansley <michael.ansley@intec.co.za> 1998-12-03 23:23 ` Re: [SQL] Optimizations Bruce Momjian <maillist@candle.pha.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Michael Ansley @ 1998-12-03 12:14 UTC (permalink / raw) To: pgsql-sql Please would somebody tell me whether or not the optimizer sufficiently optimizes a cross join like this: SELECT * FROM table1 t1, table2, t2 WHERE t1.field1 = t2.field4 to SELECT * FROM table1 t1 INNER JOIN table2 t2 ON t1.field1 = t2.field4 As I understand it, both queries will always return the same results, however, the second query will execute substantially faster, particularly as the tables involved increase in number of rows. It seems that most people prefer the first syntax, as it is probably quite simple logically, however, I suspect that the optimizer does not optimize it absolutely. Also, when LEFT, and OUTER JOINs are required, people forget that the JOIN keyword exists, and try to use the same syntax as in the first query. If I wanted all records in table1, and only associated records from table2, then the only way that I know of to retrieve this information would be as follows: SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.field1 = t2.field4 Would somebody please enlighten me (with regard to the optimizer, as well as alternatives for the last query above). I suspect that a lot of people are inadvertently using cross joins, when that is not what they mean to be doing, and are not impressed with the speed. Thanks... ---------------------------------------- Michael Ansley Intec (Ireland) Tel : +27 21 430-9000 Cell : +27 82 784-4229 eMail: michael.ansley@intec.co.za ICQ : 23465105 ---------------------------------------- ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] Optimizations 1998-12-03 12:14 Optimizations Michael Ansley <michael.ansley@intec.co.za> @ 1998-12-03 23:23 ` Bruce Momjian <maillist@candle.pha.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Bruce Momjian @ 1998-12-03 23:23 UTC (permalink / raw) To: michael.ansley@intec.co.za; +Cc: pgsql-sql > Please would somebody tell me whether or not the optimizer sufficiently optimizes a cross join like this: > SELECT * > FROM table1 t1, table2, t2 > WHERE t1.field1 = t2.field4 > > to > SELECT * > FROM table1 t1 > INNER JOIN table2 t2 > ON t1.field1 = t2.field4 Using this is a sign of an SQL engine with a bad or nonexistant optimizer. We have a good optimizer which will properly handle such queries. > > As I understand it, both queries will always return the same results, however, the second query will execute substantially faster, particularly as the tables involved increase in number of rows. It seems that most people prefer the first syntax, as it is probably quite simple logically, however, I suspect that the optimizer does not optimize it absolutely. > Also, when LEFT, and OUTER JOINs are required, people forget that the JOIN keyword exists, and try to use the same syntax as in the first query. If I wanted all records in table1, and only associated records from table2, then the only way that I know of to retrieve this information would be as follows: > SELECT * > FROM table1 t1 > LEFT JOIN table2 t2 > ON t1.field1 = t2.field4 > > Would somebody please enlighten me (with regard to the optimizer, as well as alternatives for the last query above). I suspect that a lot of people are inadvertently using cross joins, when that is not what they mean to be doing, and are not impressed with the speed. We don't support outer joins yet. -- Bruce Momjian | http://www.op.net/~candle maillist@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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1998-12-03 23:23 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 1998-12-03 12:14 Optimizations Michael Ansley <michael.ansley@intec.co.za> 1998-12-03 23:23 ` Bruce Momjian <maillist@candle.pha.pa.us>
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