MINUS and slow 'not in'

Поиск
Список
Период
Сортировка
От pierre
Тема MINUS and slow 'not in'
Дата
Msg-id 199811240453.EAA50170@out4.ibm.net
обсуждение исходный текст
Ответы Re: [SQL] MINUS and slow 'not in'  (Herouth Maoz <herouth@oumail.openu.ac.il>)
Список pgsql-sql
All,
  I've got a small problem.

Say you have tables A and B. They both have a userid column. Table B was
selected and previously filled with entries from table A. Lets say about
2000 out of 40,000. Now
I want to select everything from A that isn't in B, so about 38,000
entries.

I can't seem to get the MINUS to work within a select statement all I
ever get are
parse errors. Is this even implemented yet?

I then tried using a 'not in' clause.

select * from A where user_id not in (select * from B);

This is VERY slow, and examining the explain output tells me that it will
use the user_id index for table B, but a sequential scan of A even though
A has an index for the user_id column.

Am I missing something? Does anyone have any ideas?

Thanks for any help.

-=pierre

В списке pgsql-sql по дате отправления:

Предыдущее
От: Engard Ferenc
Дата:
Сообщение: select in update
Следующее
От: Herouth Maoz
Дата:
Сообщение: Re: [SQL] select in update