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