Hello everyone,
I'm currently in the process of upgrading our PostgreSQL installations
from 9.6 to 13. I am experiencing very slow query performance for empty
tables.
Our test environments get build from scratch every run, and thus contain
a lot of empty tables at first. We hit the issue discussed and resolved
in [1]. Problem is, this fix is not included in PG13... but JIT is :)
Some of our queries join a lot of tables, so the planner thinks this
will result in very high costs, and the JIT feature kicks in. This gives
bad performance. Executing the query is very fast of course; it's just
the planning stage that is time consuming. We do not see this behavior
in PG9.6, as there is no JIT. EXPLAIN gives the same high costs on PG9.6
and PG13, but on PG9.6 the planning time is low enough.
Now, how can I circumvent this?
- I could wait for PG14. I verified that PG14 solves my case and queries
are fast. But this would take at least until the third quarter of 2021,
so the website tells me.
- I could ask for a back-port of [2]. The commit is API breaking; is
this even a feasible option?
- I could turn off JIT in the server config, but I'd like to use the JIT
feature where it's appropriate!
- I could turn off JIT using a GUC prior to query execution. But then I
would need to detect the cases where I need to do this. (I.e. cases
where I query a table that has no tuples.) It would be very cumbersome
for me to write this in my application, and I feel this is more the
responsibility of the database than of my program.
- Another way is to fake the number of pages; set it to 1 where it is 0
everywhere. I verified that this produces fast query performance. But
fiddling with pg_class does not "feel" right... Is this okay to use in a
test setup (and maybe even in a production scenario)? Could I do the
following query just after our database is initialized:
update pg_catalog.pg_class c set relpages = 1 where c.relpages = 0 and
c.relnamespace = (select ns.oid from pg_catalog.pg_namespace ns where
ns.nspname = current_schema);
Could someone give me advice on what would be the best strategy?
With kind regads,
Jurrie
[1] https://postgr.es/m/F02298E0-6EF4-49A1-BCB6-C484794D9ACC@thebuild.com
[2]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=3d351d916b20534f973eda760cde17d96545d4c4
