From: | "Mark Cowlishaw" <markc(at)ot(dot)com(dot)au> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | not using table aliases in where clause slow-down? |
Date: | 2001-02-19 06:46:14 |
Message-ID: | 004d01c09a3f$a748d800$5250460a@meta2k |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)ot(dot)com(dot)au>
Open Telecommunications
1/116 Miller St. North Sydney
Phone: +61 2 8925 3192
Mobile: 0402 142 424
From | Date | Subject | |
---|---|---|---|
Next Message | Karel Zak | 2001-02-19 08:27:31 | Re: .TO_CHAR problem |
Previous Message | Lincoln Yeoh | 2001-02-19 06:26:57 | Re: select ... for update limit 1 |