weird query plans involving hash [join] & no indicies

From: Brett McCormick <brett(at)abraxas(dot)scene(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: weird query plans involving hash [join] & no indicies
Date: 1998-01-27 08:44:50
Message-ID: 199801270844.AAA07540@abraxas.scene.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


The following two queries & explains seem very strange. the two
statements differ only by the absence of usrexists = 't' in the where
clause of the second, but it seems far less effecient (rather than
more?)

At first I thought my hash index on usrid should be used for the first
example, but obviously it can't use the index on both. Is it possible
for it to use an Index scan on the bigger table (in this case, user)?

The second one seems totally insane!

--brett

sas=> explain update user set usrfreextime = 't' from xtermaccess where usrexists = 't' and usrid = xtausrid ;
NOTICE:QUERY PLAN:

Nested Loop (cost=701.57 size=1 width=225)
-> Seq Scan on user (cost=700.52 size=1 width=217)
-> Index Scan on xtermaccess (cost=1.05 size=4210 width=8)

EXPLAIN

sas=> explain update user set usrfreextime = 't' from xtermaccess where usrid = xtausrid ;
NOTICE:QUERY PLAN:

Hash Join (cost=1206.89 size=5652 width=225)
-> Seq Scan on user (cost=700.52 size=5652 width=217)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on xtermaccess (cost=178.93 size=4210 width=8)

EXPLAIN
sas=>

Browse pgsql-hackers by date

  From Date Subject
Next Message Serj 1998-01-27 08:49:33 Snapshot 270198 compile error
Previous Message Vadim B. Mikheev 1998-01-27 08:40:58 Re: [PORTS] Port Bug Report: Result not "GROUPED BY" from SQL: select im,bn,count(adr) FROM logtmp GROUP BY im,bn;