Forum OpenACS Q&A: Faster Selects with study of EXPLAIN

if you are running 7.2 try running "explain analyze" which actually executes the query so you're not just guesstimating.

First of all, the cost estimates should not be used to attempt to decide which of the two approaches is faster.

If you look at the estimate on the cost of the select on the users_contact table in the JOIN query you'll see it is estimating that roughly ten times as many rows will be returned than in the non-join case.  This is clearly bogus, as only one row will be returned for the entire query.  That 10x factor right there explains why EXPLAIN makes you think the query will run at least 9x slower than doing the two separately.

These cost estimates are only used by the optimizer to decide what kind of scans and joins to use, they're not intended for anything else.  The estimates are known to be relatively obtuse as they are based on very minimal statistics kept for the table.  PG 7.2 keeps additional statistics which should help the optimizer make better decisions, but the underlying notion that the cost estimate is just an estimate has not changed.

In general a join with a good plan will run faster than two queries.

Stacking the deck further against breaking your queries up, two queries mean two round-trips from the driver to the DB, two parses, two optimization passes, two executions with two resultsets being passed back to the drive, stuffed into Tcl vars, and then read by your code.

As far as your view question goes, PG executes views dynamically so normally the execution time will be roughly the time taken for the query written out by hand.

Thanks James for the added info.  Now I have an idea why I use those join....hehehe.

Anyway it seems that explicit joins are good, is it advised that OpenACS use explicit joins if possible?

No, I do *not* recommend using the explicit JOIN syntax as a general rule.  For one thing, as Tom Lane's note indicates, the optimizer doesn't fully optimize complex JOIN expressions in the FROM list.  For another the syntax isn't supported by Oracle 8i and we want to maintain portable queries as much as possible, so gratuitous differences are a no-no.

IMO the optimization issue is a bug but thus far the PG folk seem to take the point of view that it's a feature.  "You can constrain the optimizer by writing explicit JOINs or get full optimization by writing implicit JOINs!"  Hogwash.  In an ideal world all JOINs would be written in the FROM list, as this is the preferred SQL92 standard way to do it, but if the optimizer's going to punt on them then it isn't a good idea.  The reason the optimizer punts is because outer JOINs aren't associative, and the code doesn't differentiate between outer and inner JOINs when optimizing (i.e. it treats inner JOINs as though they're not associative).  The PG folks are used to the implicit join syntax and thus seem to think the only reason folks might use it is in order to take "advantage" of the fact that there's only limited optimization done on the explicit joins.

So ... the bottom line is that inner JOINs written in the FROM list are only optimized in a limited way.  Given that the best join order will vary depending on the size of various tables, in general it isn't possible to write queries to explicitly express the "best" way to JOIN, because the "best" will vary from installation to installation.  An installation with lots of users and few groups, for instance, may perform better with joins against parties and relationships done in a different order than an installation with lots of groups relative to the number of users.