Postgres 11 chooses seq scan instead of index-only scan
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 = 2048MBwork_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: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
twoflower <standa.kurik@gmail.com> writes:
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
You might need to vacuum the table to ensure that the planner thinks
a reasonable proportion of the pages are all-visible (see
pg_class.relallvisible).
regards, tom lane
Yes! That was it, after running VACUUM TABLE history_translation, the query
is now executed using index-only scan.
I was under the impression that ANALYZE TABLE history_translation is enough,
but it is not.
Thank you very much.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
On Thu, Jan 24, 2019 at 9:01 AM twoflower <standa.kurik@gmail.com> wrote:
Yes! That was it, after running VACUUM TABLE history_translation, the
query is now executed using index-only scan.I was under the impression that ANALYZE TABLE history_translation is
enough, but it is not.
Only a VACUUM will update the visibility map.
https://www.postgresql.org/docs/current/storage-vm.html
I used to think the same, that ANALYZE was enough, coming from an Oracle
background. I learned later that the visibility map isn't just used to
determine what to vacuum, but it is used by the optimizer/planner when
evaluating execution plans.
--
Don Seiler
www.seiler.us