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 по дате отправления:

Предыдущее
От: Goran Thyni
Дата:
Сообщение: Re: [HACKERS] postmaster crash and .s.pgsql file
Следующее
От: Serj
Дата:
Сообщение: Snapshot 270198 compile error