Postgres 11 chooses seq scan instead of index-only scan

[twoflower <standa.kurik@xxxxxxxxx>]

I restored a dump of our production DB (running on 9.6) to a Postgres 11 server and wanted to run some basic benchmarks to see if there isn't some unexpected performance drop.


One issue I cannot resolve is the new server using a parallel seq scan instead of index-only scan for the following query:



select count(id) from history_translation


The table has about 123 million rows. The servers use identical configuration. The hardware is similar (4 cores and 18 GB RAM for the 9.6 server vs. 26 GB RAM for the new one). In particular, all the *_cost settings have the default value and the only possibly relevant settings with non-default value are

shared_buffers = 2048MB
work_mem = 32MB


The query finishes in 39 seconds on the 9.6 server and in 2 minutes on the 11 server.


Even when I effectively disable parallel queries (using set max_parallel_workers_per_gather = 0), the new server chooses sequential scan and, of course, takes much longer to finish the query. I tried recreating the index and analyzing the table again, but it did not change anything.


Any help will be welcome.

---

Sent from the PostgreSQL - general mailing list archive at Nabble.com.