table aliasing problem with 6.5...

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=25752 width=40)
-> Seq Scan on dirs d (cost=5.52 rows=137 width=20)
-> Seq Scan on dirs (cost=5.52 rows=137 width=4)
-> Hash (cost=1205.82 rows=25752 width=4)
-> Seq Scan on picdata (cost=1205.82 rows=25752 width=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 using pd_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)
-> Seq Scan 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.21 rows=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(at)toodarkpark(dot)org> URL: http://www.toodarkpark.org
"The distance between insanity and genius is measured only by success."