query slowness caused by table alias
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.
The system: Linux RedHat 6.0, running postgresql
7.0RC4
The bug: The mixed uses of table names and table alias
in a query results in an extra sequential scan in
query execution
Test cases:
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)
# note: a seq scan appears
EXPLAIN
SELECT *
FROM part -- take out the alias
WHERE part.part_id = 42;
Index Scan using part_pkey on part (cost=0.00..3.60
rows=1 width=215)
# note: now the seq scan is gone
EXPLAIN
SELECT *
FROM part p
WHERE p.part_id = 42; -- use alias only
Index Scan using part_pkey on part p (cost=0.00..3.60
rows=1 width=215)
# note: it's also ok
Thanks,
Chi-Fung
__________________________________________________
Do You Yahoo!?
Send instant messages & get email alerts with Yahoo! Messenger.
http://im.yahoo.com/
Chi Fan <chifungfan@yahoo.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
On Thu, 4 May 2000, Tom Lane wrote:
EXPLAIN
SELECT *
FROM part p -- let p alias to part
WHERE part.part_id = 42; -- but don't use pNested 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.
Isn't it time we disable this by default or at least provide an option for
it? It seems this comes up every week.
--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden