not using table aliases in where clause slow-down?

From: "Mark Cowlishaw" <markc@ot.com.au>

I noticed that running queries that do -not- use declared table aliases in
the 'where' clause seem to run a hell-of-a-lot slower than when aliases

are

used. Is there a valid reason for this? It started out as a typo but now

I'm

curious.

(7.0.3)

eg:

select

[snip]

from
releases as rel, subsystems as subs,
functions as func, purposes as purp, procedures as proc
where
rel.project_id = 53

[snip]

(runs pretty much instantly)

-versus-

select

[snip]

from
releases as rel, subsystems as subs,
functions as func, purposes as purp, procedures as proc
where
releases.project_id = 53

[snip]

(I get sick of waiting after > 30sec and ^C the query (in psql))

--
Mark Cowlishaw <markc@ot.com.au>

I think the second where refers to a different "releases" so it's equivalent
to doing a join without a joining condition (set product? can't remember -
need more coffee). I think it's the same as putting another "releases" in
"from" is what I'm trying to say (badly)

- Richard Huxton

On Mon, 19 Feb 2001, Mark Cowlishaw wrote:

I noticed that running queries that do -not- use declared table aliases in
the 'where' clause seem to run a hell-of-a-lot slower than when aliases are
used. Is there a valid reason for this? It started out as a typo but now I'm
curious.

Well, sort of. Technically the second query is invalid (you cannot use
the non-aliased name if you've aliased IIRC), but postgres assumes that
you really wanted additional from entries for the tables you are using
to make it valid, so:

select
rel.release_id as rel_id, rel.code as rel_code,
subs.subsystem_id as subs_id, subs.code as subs_code,
func.function_id as func_id, func.code as func_code,
purp.purpose_id as purp_id, purp.code as purp_code,
purp.title as purp_title,
proc.procedure_id as proc_id, proc.code as proc_code,
proc.title as proc_title
from
releases as rel, subsystems as subs,
functions as func, purposes as purp, procedures as proc
where
releases.project_id = 53
and purposes.release_id = releases.release_id
and purposes.function_id = functions.function_id
and functions.subsystem_id = subsystems.subsystem_id
and purposes.purpose_id = procedures.purpose_id
order by
rel.code, subs.code, func.code,
purp.code, proc.code;

is really:

select
rel.release_id as rel_id, rel.code as rel_code,
subs.subsystem_id as subs_id, subs.code as subs_code,
func.function_id as func_id, func.code as func_code,
purp.purpose_id as purp_id, purp.code as purp_code,
purp.title as purp_title,
proc.procedure_id as proc_id, proc.code as proc_code,
proc.title as proc_title
from
releases as rel, subsystems as subs,
functions as func, purposes as purp, procedures as proc,
***
releases, functions, subsystems, purposes, procedures
***
where
releases.project_id = 53
and purposes.release_id = releases.release_id
and purposes.function_id = functions.function_id
and functions.subsystem_id = subsystems.subsystem_id
and purposes.purpose_id = procedures.purpose_id
order by
rel.code, subs.code, func.code,
purp.code, proc.code;

which is a much different query