Joining tables by UUID field - very slow

Поиск
Список
Период
Сортировка
От Cherio
Тема Joining tables by UUID field - very slow
Дата
Msg-id CAKHqFk+BOgDe=3B9P0mvtviTN+bcO1XvL0k50BVQqiVWRjF7eg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Joining tables by UUID field - very slow  (Merlin Moncure <mmoncure@gmail.com>)
Re: Joining tables by UUID field - very slow  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
We are experiencing an unusual slowdown when using UUID field in JOIN when updating a table. SQL looks like this:

UPDATE dst
SET data_field = src.data_field
FROM src
WHERE dst.uuid_field = src.uuid_field;

This statement takes over 6 times longer than a similar statement against the same table except the join is done by a integer field, e.g.

UPDATE dst
SET data_field = src.data_field
FROM src
WHERE dst.integer_field = src.integer_field;

I can't get rid of UUID in the "src" table since it comes from another database that we can't change. The table has around 1 mil rows. I tried vacuuming it. Tried creating indexes on src table (it ignores them and builds hash join anyway). It takes faster to rebuild the whole table than to update it while joining by UUID. Has anyone experienced this before and what was the solution for you?

Help is greatly appreciated.

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

Предыдущее
От: Wales Wang
Дата:
Сообщение: 回复: [PERFORM] PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?
Следующее
От: Cédric Villemain
Дата:
Сообщение: Re: PG as in-memory db? How to warm up and re-populate buffers? How to read in all tuples into memory?