Re: Scrub one large table against another

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Scrub one large table against another
Дата
Msg-id 13451.1160518445@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Scrub one large table against another  (Brendan Curran <brendan.curran@gmail.com>)
Ответы Re: Scrub one large table against another  (Brendan Curran <brendan.curran@gmail.com>)
Список pgsql-performance
Brendan Curran <brendan.curran@gmail.com> writes:
> CREATE TEMP TABLE temp_list_suppress(email_record_id int8);

> INSERT INTO temp_list_suppress
>     SELECT email_record_id from ONLY email_record er
>     WHERE email_list_id = 9 AND email IN
>     (select email from suppress);

> CREATE INDEX unique_id_index on temp_list_suppress ( email_record_id );

> INSERT INTO er_banned
> SELECT * from ONLY email_record er WHERE EXISTS
> (SELECT 1 from temp_list_suppress ts where er.email_record_id =
> ts.email_record_id)';

> DELETE FROM ONLY email_record WHERE email_list_id = 9 AND email_record_id IN
>     (SELECT email_record_id from temp_list_suppress);

> TRUNCATE TABLE temp_list_suppress;
> DROP TABLE temp_list_suppress;

> The performance is dreadful, is there a more efficient way to do this?

Have you tried doing EXPLAIN ANALYZE of each of the INSERT/DELETE steps?
If you don't even know which part is slow, it's hard to improve.

It would probably help to do an "ANALYZE temp_list_suppress" right after
populating the temp table.  As you have it, the second insert and delete
are being planned with nothing more than a row count (obtained during
CREATE INDEX) and no stats about distribution of the table contents.

Also, I'd be inclined to try replacing the EXISTS with an IN test;
in recent PG versions the planner is generally smarter about IN.
(Is there a reason why you are doing the INSERT one way and the
DELETE the other?)

BTW, that TRUNCATE right before the DROP seems quite useless,
although it's not the main source of your problem.

            regards, tom lane

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

Предыдущее
От: Brendan Curran
Дата:
Сообщение: Scrub one large table against another
Следующее
От: Brendan Curran
Дата:
Сообщение: Re: Scrub one large table against another