weird query plans involving hash [join] & no indicies
От | Brett McCormick |
---|---|
Тема | weird query plans involving hash [join] & no indicies |
Дата | |
Msg-id | 199801270844.AAA07540@abraxas.scene.com обсуждение исходный текст |
Список | 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=>
В списке pgsql-hackers по дате отправления: