Надо обновить 1000 записей в одной большой таблице значениями из другой, связь по двум полям, в обеих таблицах создан индекс по этим полям. Выборка 1000 по первичному ключу.
В Акцессе создал точно такую структуру и обновил 1 000 000 записей примерно за минуту.
Пока я пишу эту мессагу Postgre еще шуршит, прошло уже 5 минуты и ....что не так?
CREATE TABLE vin
(
...
vid serial, -- PK
tom character(3), -- ix
ind2 integer, -- ix
specid integer,
CONSTRAINT pk_vid PRIMARY KEY (vid)
)
CREATE INDEX ix_tommod
ON vin
USING btree
(tom, ind2);
CREATE TABLE specvin
(
tom character(3) , --ix
pos integer , --ix
id serial , --pk
CONSTRAINT pk_id PRIMARY KEY (id)
)
CREATE INDEX ix_vinspec
ON specvin
USING btree
(tom, pos)
WITH (FILLFACTOR=100);
update vin
set specid = sp.id
FROM
public.vin v,
public.specvin sp
WHERE
v.tom = sp.tom AND
v.ind2 = sp.pos
AND v.vid <1000;
Прошло 20 минут, остановил запрос.
select *
FROM
public.vin v,
public.specvin sp
WHERE
v.tom = sp.tom AND
v.ind2 = sp.pos
AND v.vid <1000;
Выполнился за 346 ms, меньше чем за полсекунды.
Что за ... с апдейтом?
==========
дали ответ на sql.ru,
синтаксис свой
update vin v
set specid = sp.id
FROM
public.specvin sp
WHERE
v.tom = sp.tom AND
v.ind2 = sp.pos
AND v.vid <1000;
Предположений несколько. 1.
Предположений несколько.
1. Транзакционность. В access нет транзакций, здесь же каждая обновлённая строка должна быть записана на диск.
2. Индексы. Каждый update требует внесение соответствующих изменений в индекс. Где-то в мануале встречал замечание о том, что если вы производите большую вставку или обновление данных и есть такая возможность, то лучше удалить индексы на время этих операций, а потом создать их заново.
3. Использование WHERE там где можно было бы попробовать использовать JOIN
4. Большая таблица. Если у вас в таблице миллионы записей - это тоже может сыграть большую роль. Возможно имеет смысл попробовать разбиение (partitioning)?
Вообще мне кажется не лишним было бы прогнать план запроса и посмотреть что происходит. Возможно это позволит понять суть проблемы.
дело в особенностях синтаксиса оказалось
В Postgres нельзя повторять в предложении FROM обновляемую таблицу. Это несколько неудобно на мой взгляд, но такова действительность.
update table_1
set ....
FROM
-- table_1, некорректно
table_2
WHERE
.....
Ээээ.... Странно. Поскольку
Ээээ.... Странно. Поскольку PostgreSQL стремится соответствовать стандартам, то либо это баг, либо синтаксис совершенно правильный, а вот неправильный как раз у других. Насколько я понял мануал FROM ...WHERE несколько отлично от JOIN работают, может таки в этом дело и используя JOIN вы бы на эти грабли не наступили?