table aliasing problem with 6.5... - Mailing list pgsql-sql

odd problem in postgres 6.5.  

basically, if you use aliased tables _and_ use the full table name,
postgres goes nuts.  ive verified this on two separate databases
with the same postgres installation. (PostgreSQL 6.5.0 on
i686-pc-linux-gnu, compiled by egcc)

pics=> explain
pics-> SELECT pd.picnum, pd.picname, d.dirid, d.diskid, pd.tstamp,
pd.mdsum, pd.fsize,
pics-> d.dirname FROM picdata pd,dirs d WHERE picdata.dirid=dirs.dirid;
NOTICE:  QUERY PLAN:

Hash Join  (cost=35573920.00 rows=1073741849 width=68) ->  Nested Loop  (cost=19621602.00 rows=483339288 width=64)
->  Nested Loop  (cost=143382.61 rows=3528024 width=60)             ->  Seq Scan on picdata pd  (cost=1205.82
rows=25752width=40)             ->  Seq Scan on dirs d  (cost=5.52 rows=137 width=20)       ->  Seq Scan on dirs
(cost=5.52rows=137 width=4) ->  Hash  (cost=1205.82 rows=25752 width=4)       ->  Seq Scan on picdata  (cost=1205.82
rows=25752width=4)
 

picdata contains 25752 rows.
dirs contains 137 rows.

483339288 is a cartesian join across (seemingly) pd, d, and dirs.
1073741849 doesnt seem to be anything that i can figure out.
3528024 is from a cartesian join across pd and d ( or picdata and dirs )

but changing 'dirs.dirid' to 'd.dirid' and 'picdata.dirid' to 'pd.dirid' (
using the alised tables ) fixes the excessive row problem:

pics=> explain
pics-> SELECT pd.picnum, pd.picname, d.dirid, d.diskid, pd.tstamp,
pd.mdsum, pd.fsize,
pics-> d.dirname FROM picdata pd,dirs d WHERE pd.dirid=d.dirid;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=1202.40 rows=25752 width=64) ->  Seq Scan on dirs d  (cost=5.52 rows=137 width=20) ->  Index Scan
usingpd_dirid_idx on picdata pd  (cost=8.74 rows=25752 width=44)
 

with another database:

ircbot=> explain 
ircbot-> SELECT u.usernum, u.nick, u.botnick, u.passwd, um.hostmask
ircbot-> FROM users u, usermasks um
ircbot-> WHERE users.usernum=usermasks.usernum;
NOTICE:  QUERY PLAN:

Hash Join  (cost=50951.80 rows=3932364 width=60) ->  Nested Loop  (cost=30348.58 rows=623971 width=56)       ->  Nested
Loop (cost=444.54 rows=9313 width=52)             ->  Seq Scan on users u  (cost=3.21 rows=67 width=40)             ->
SeqScan on usermasks um  (cost=6.59 rows=139 width=12)       ->  Seq Scan on users  (cost=3.21 rows=67 width=4) ->
Hash (cost=6.59 rows=139 width=4)       ->  Seq Scan on usermasks  (cost=6.59 rows=139 width=4)
 

ircbot=> explain 
ircbot-> SELECT u.usernum, u.nick, u.botnick, u.passwd, um.hostmask
ircbot-> FROM users u, usermasks um
ircbot-> WHERE u.usernum=um.usernum;
NOTICE:  QUERY PLAN:

Hash Join  (cost=17.60 rows=423 width=56) ->  Seq Scan on usermasks um  (cost=6.59 rows=139 width=16) ->  Hash
(cost=3.21rows=67 width=40)       ->  Seq Scan on users u  (cost=3.21 rows=67 width=40)
 

so, questions:

(1) why is it trying to do a cartesian join when not using the aliased   tables?
(2) is this 'bug' present in 6.5.1 ?
(3) why am i still awake at 7.30am EST on a saturday?

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org    
"The distance between insanity and genius is measured only by success."