Блог

Главная - Вопросы по программированию - Оптимизировать этот простой SQL-запрос?

Оптимизировать этот простой SQL-запрос?

#sql #postgresql #query-optimization

Вопрос:

У меня есть запрос, который использует dblink для заполнения данных из одной базы данных и вставки их в столбец jsonb в другой базе данных. Он должен работать только с 1000 записями или около того, но для этого требуется много времени (от 30 секунд до минуты).

Почему? Как мне сделать это быстрее?

Вот запрос:

 CREATE EXTENSION dblink;
update "table" t1 set jsonb_example_column = jsonb_set(jsonb_example_column, '{user}', jsonb_build_object('name',jsonb_example_column->'user'->'name','phone',jsonb_example_column->'user'->'phone','address',jsonb_example_column->'user'->'address','user_id', other_db.user_id)) 
FROM dblink('dbname=$DB_NAME
            port=$DB_PORT
            host=$DB_HOST
            user=$DB_USER
            password=$DB_PASSWORD',
            'SELECT user_id, external_id from other_db_table t2')
AS other_db("user_id" uuid, external_id uuid) WHERE other_db.external_id = t1.external_id;"

Комментарии:

1. Это удаленная таблица, поэтому проверьте: а) сетевое подключение к удаленному серверу, б) объем запрашиваемых данных. Если вы запрашиваете много данных при медленном подключении — тогда здесь нет ничего неожиданного

2. Вы пробовали, быстрее ли использовать внешнюю таблицу?

3. «Ему нужно работать только на 1000». Это размер «other_db_table»? Если нет, то откуда берется это число?

4. Я не пробовал иностранный стол, но сделаю это. и да, ~1000-это размер «other_db_table».

5. Так что иностранный стол был намного быстрее. Спасибо, a_horse_with_no_name!

Ответ №1:

Ответ дан благодаря a_horse_with_no_name, но вот оптимизированный запрос, который теперь занимает треть времени:

 CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER user FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '0.0.0.0', port '5432', dbname 'user');
CREATE USER MAPPING FOR postgres SERVER user OPTIONS (user 'your_foreign_db_user', password 'your_foreign_db_password');
GRANT USAGE ON FOREIGN SERVER user TO postgres;
CREATE FOREIGN TABLE IF NOT EXISTS foreign_table(user_id uuid, external_id uuid) SERVER user OPTIONS (schema_name 'public', table_name 'foreign_db_table');

UPDATE "local_db_table" lt SET jsonb_example_column = jsonb_set(jsonb_example_column, '{user}', jsonb_build_object('name',jsonb_example_column->'user'->'name','phone',jsonb_example_column->'user'->'phone','address',jsonb_example_column->'user'->'address','user_id', ft.user_id))
FROM foreign_table AS ft WHERE lt.external_id = ft.external_id;
Метки: