Thread: not using table aliases in where clause slow-down?

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
        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
        rel.project_id        = 53
        and purp.release_id   = rel.release_id
        and purp.function_id  = func.function_id
        and func.subsystem_id = subs.subsystem_id
        and purp.purpose_id   = proc.purpose_id
    order by
        rel.code, subs.code, func.code,
        purp.code, proc.code;

(runs pretty much instantly)

-versus-


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;

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

--
Mark Cowlishaw <markc@ot.com.au>
Open Telecommunications
1/116 Miller St. North Sydney
Phone: +61 2 8925 3192
Mobile: 0402 142 424

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