Queue table that quickly grows causes query planner to choose poor plan

Поиск
Список
Период
Сортировка
От David Wheeler
Тема Queue table that quickly grows causes query planner to choose poor plan
Дата
Msg-id 64CC9527-C642-4AED-844B-70E7BC996A1B@dgitsystems.com
обсуждение исходный текст
Ответы Re: Queue table that quickly grows causes query planner to choosepoor plan  (Justin Pryzby <pryzby@telsasoft.com>)
Re: Queue table that quickly grows causes query planner to choose poor plan  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Hi All, 

I’m having performance trouble with a particular set of queries. It goes a bit like this

1) queue table is initially empty, and very narrow (1 bigint column)
2) we insert ~30 million rows into queue table
3) we do a join with queue table to delete from another table (delete from a using queue where a.id = queue.id), but postgres stats say that queue table is empty, so it uses a nested loop over all 30 million rows, taking forever

If I kill the query in 3 and let it run again after autoanalyze has done it’s thing then it is very quick

This queue table is empty 99% of the time, and the query in 3 runs immediately after step 2. Is there any likelyhood that tweaking the autoanalyze params would help in this case? I don’t want to explicitly analyze the table between steps 2 and three either as there are other patterns of use where for example 0 rows are inserted in step 2 and this is expected to run very very quickly. Do I have any other options?

Postgres 9.5 ATM, but an upgrade is in planning. 


Thanks in advance

David Wheeler
Software developer



dwheeler@dgitsystems.com
D +61 3 9663 3554  W http://dgitsystems.com
Level 8, 620 Bourke St, Melbourne VIC 3000.


Вложения

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

Предыдущее
От: Elvir Kurić
Дата:
Сообщение: Re: "set primary keys..." is missing when using hight values fortransactions / scaling factor with pgbench
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: Slow join