Re: insert and query performance on big string table with pg_trgm

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: insert and query performance on big string table with pg_trgm
Дата
Msg-id CAMkU=1yojKJsiXWqEsJX4g1g_OqNu=mRmdiQxcOBhXoA5EO2bQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: insert and query performance on big string table with pg_trgm  (Matthew Hall <mhall@mhcomputing.net>)
Ответы Re: insert and query performance on big string table with pg_trgm
Список pgsql-performance
Дерево обсуждения
On Nov 21, 2017 00:05, "Matthew Hall" wrote: > Are all indexes present at the time you insert? It will probably be much faster to insert without the gin index (at least) and build it after the load. There is some flexibility on the initial load, but the updates in the future will require the de-duplication capability. I'm willing to accept that might be somewhat slower on the load process, to get the accurate updates, provided we could try meeting the read-side goal I wrote about, or at least figure out why it's impossible, so I can understand what I need to fix to make it possible. As long as you don't let anyone use the table between the initial load and when the index build finishes, you don't have to compromise on correctness. But yeah, makes sense to worry about query speed first. > If you repeat the same query, is it then faster, or is it still slow? If you keep the expression exactly the same, it still takes a few seconds as could be expected for such a torture test query, but it's still WAY faster than the first such query. If you change it out to a different expression, it's longer again of course. There does seem to be a low-to-medium correlation between the number of rows found and the query completion time. To make this quick, you will need to get most of the table and most of the index cached into RAM. A good way to do that is with pg_prewarm. Of course that only works if you have enough RAM in the first place. What is the size of the table and the gin index? Cheers, Jeff

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Query became very slow after 9.6 -> 10 upgrade
Следующее
От: Gábor SZŰCS
Дата:
Сообщение: Re: insert and query performance on big string table with pg_trgm