Skip site navigation (1) Skip section navigation (2)

Re: query slowness caused by table alias

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chi Fan <chifungfan(at)yahoo(dot)com>
Cc: bugs(at)postgresql(dot)org
Subject: Re: query slowness caused by table alias
Date: 2000-05-05 03:25:11
Message-ID: 4405.957497111@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Chi Fan <chifungfan(at)yahoo(dot)com> writes:
> I don't know if this's really a bug in 7.0, but I
> don't see this
> problem/feature in 6.5.3.

> EXPLAIN 
> SELECT * 
> FROM part p              -- let p alias to part
> WHERE part.part_id = 42; -- but don't use p

> Nested Loop  (cost=0.00..2429.30 rows=50735 width=219)
>   -> Index Scan using part_pkey on part (cost=0.00..3.60 rows=1 width=4)
>   -> Seq Scan on part p  (cost=0.00..1918.35 rows=50735 width=215)

This is correct behavior.  Since you aliased part as p, the *only* way
to refer to that FROM entry elsewhere in the query is 'p'.  'part' is a
second reference to the table, resulting in a self-join, just as if you
had written "FROM part p, part part WHERE ...".  The only difference is
that '*' expands to just the fields of tables directly referenced in
FROM, so you don't see two copies of the fields.  (Maybe if you did,
the mistake would be more obvious...)

AFAIK, 6.5 and earlier versions behave the same way as 7.0 on this point.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2000-05-05 04:12:40
Subject: Re: Need some help: attlen is pg_attributes gives a negative value.. .
Previous:From: Chi FanDate: 2000-05-05 02:57:45
Subject: query slowness caused by table alias

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group