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
Re: Queue table that quickly grows causes query planner to choose poor plan |
Список | pgsql-performance |
Hi All,

E dwheeler@dgitsystems.com
D +61 3 9663 3554 W http://dgitsystems.com
Level 8, 620 Bourke St, Melbourne VIC 3000.
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
Software developer

E dwheeler@dgitsystems.com
D +61 3 9663 3554 W http://dgitsystems.com
Level 8, 620 Bourke St, Melbourne VIC 3000.
Вложения
В списке pgsql-performance по дате отправления: