Thread

1. query slowness caused by table alias

   Chi Fan <chifungfan@yahoo.com> — 2000-05-05T02:57:45Z

   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/
   
   

2. Re: query slowness caused by table alias

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-05-05T03:25:11Z

   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
   
   

3. Re: query slowness caused by table alias

   Peter Eisentraut <e99re41@docs.uu.se> — 2000-05-05T09:41:19Z

   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 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.
   
   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