Slow planning time on PostgreSQL 12.4

Question

I have a database with Openstreetmap location data, and in particular there is a polygon table with around 500 million rows.

I created indexes on all of my queries consisting of using gist(way) where <filter of query restrictions>. The indexes work well, and the query planner selects the correct index for the query, but the planning time is always much higher than execution time, to the point where planning time might be 112ms vs execution time of 1.7ms.

This is using a the same open connection, no partitions exist as far as I know, no functions, and analyze and vacuum make no difference (no data are added or deleted anyway).

I have tried my highly modified config as well as stock config with no changes.

I was able to speed it up significantly by deleting my specialized indexes and leaving the general index USING gist( way ) with no filters, but planning time is still more than execution time. After this change the slowest query went from a planning time of about 35ms to about 3ms, with similar execution times.

So the main question is if this is normal to see, and if so, is there a way I can improve indexing or planning beyond only having an index on the way field?

An example query I have is this:

SELECT
way
FROM planet_osm_polygon
WHERE building IS NOT NULL
   AND building != 'no'
   AND way_area > 0
   AND z(17061.8) >= 14
   AND way && ST_SetSRID('BOX3D(-7529047.28608986 9450979.925292334,-7523543.820053328 9456483.391328866)'::box3d, 3857);

Before I had an index for this condition specifically, which had a planning time of 2ms, execution 0.7ms:

CREATE INDEX planet_osm_polygon_buildings
 ON planet_osm_polygon USING GIST(way)
 WHERE (building IS NOT NULL) AND (building <> 'no'::text) AND (way_area > '0'::double PRECISION);

When I deleted the query specific indexes it changed to 1ms planning time, 0.7 exec and used the general index on way, but of course added a filter for building and way_area. I tried adding back an index on the building column with the where filter, but it didn't improve the query planning or execution.

My current analyze on that query with general index on way only looks like:

Index Scan using planet_osm_polygon_way_idx on planet_osm_polygon  (cost=0.55..8.57 rows=1 width=215) (actual time=0.658..0.658 rows=0 loops=1)
  Index Cond: (way && '01030000A0110F00000100000005000000D84B4FD295B85CC1ABFE9B7DBC0662410000000000000000D84B4FD295B85CC11DC4856C6C0962410000000000000000F4C07BF435B35CC11DC4856C6C0962410000000000000000F4C07BF435B35CC1ABFE9B7DBC0662410000000000000000D84B4FD295B85CC1ABFE9B7DBC0662410000000000000000'::geometry)
  Filter: ((building IS NOT NULL) AND (building <> 'no'::text) AND (way_area > '0'::double precision))
  Rows Removed by Filter: 11
Planning Time: 0.967 ms
Execution Time: 0.691 ms

    {
      "Startup Cost": 0.55,
      "Total Cost": 8.57,
      "Plan Rows": 1,
      "Plan Width": 215,
      "Actual Startup Time": 0.745,
      "Actual Total Time": 0.746,
      "Actual Rows": 0,
      "Actual Loops": 1,
     "Shared Hit Blocks": 29,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "I/O Read Time": 0.000,
      "I/O Write Time": 0.000
    },
    "Planning Time": 1.239,
    "Triggers": [
    ],
    "Execution Time": 0.789

Any help or explanation is greatly appreciated.

EDIT: added my postgresql config, which I have also tried with settings removed and then did vacuum and analyze after restart of pgsql to no help.

# Add settings for extensions here

max_connections = 280
superuser_reserved_connections = 3
 
# Memory Settings
shared_buffers = 40GB
work_mem = '100 MB'
maintenance_work_mem = 4GB
huge_pages = try   # NB! requires also activation of huge pages via kernel params, see here for more:
                   # https://www.postgresql.org/docs/current/static/kernel-resources.html#LINUX-HUGE-PAGES
effective_cache_size = '64 GB'
effective_io_concurrency = 200   #Storage is an Intel DC NVME
 
# Monitoring
shared_preload_libraries = 'pg_stat_statements'    # per statement resource usage stats
track_io_timing=on        # measure exact block IO times
track_functions=pl        # track execution times of pl-language procedures if any
 
# Replication
wal_level = minimal     # consider using at least 'replica'
max_wal_senders = 0
synchronous_commit = off
wal_keep_segments = 130
 
# Checkpointing: 
checkpoint_timeout  = 1d 
checkpoint_completion_target = 0.9
max_wal_size = '10240 MB'
min_wal_size = '5120 MB'

default_statistics_target = 100 #Report of using higher causing slow planning time in 12.2. Previously set at 500

# WAL writing
wal_compression = on
wal_buffers = -1    # auto-tuned by Postgres till maximum of segment size (16MB by default)
 
# Background writer
bgwriter_delay = 200ms
bgwriter_lru_maxpages = 100
bgwriter_lru_multiplier = 2.0
bgwriter_flush_after = 0
 
# Parallel queries: 
max_worker_processes = 32 
max_parallel_workers = 32

#postgres12 features

max_parallel_maintenance_workers = 16
parallel_leader_participation = on
 
# Advanced features 

enable_partitionwise_join = on
enable_partitionwise_aggregate = on

jit=off #Causes major slowdown in 12
max_parallel_workers_per_gather = 0 #Causes major slowdown in 12

logging_collector = off
log_directory = 'pg_log'
log_min_duration_statement = 50
statement_timeout = 0

Answer 1

As you have seen, having many similar indexes on a table is not a good idea: data modification time and query planning time will increase (the latter because the optimizer has to consider all indexes).

A planning time of 3 milliseconds for a simple query like this with a single GiST index is surprisingly high, at least if you didn't do something crazy like lowering geqo_threshold to 1.

The ultimate way to speed up planning time is to use a prepared statement. Then you might get a generic plan from the sixth execution on, and planning time should go down to almost zero.

Answer 2

After messing with this problem for weeks, and even swapping out to an entirely new server, I have somewhat come up with solutions that some might find helpful. This very well may be, and I really hope it is, a symptom of a different problem. Nevertheless, my current answer is that the PostgreSQL 12 with postgis query planner is extremely slow and has terrible optimization.

So here are some things I have found:

• Check every function, even if they are simple math. The standard log() function for example (logarithm, not writing to a log) can take over 0.1-0.2ms to run depending on base cpu speed. You may be better off calculating using a different function that is much quicker.
• PostgreSQL will do work on empty data. So for example, a simple select where statement took 2ms to process and returned 0 results. Wrapping that select with a more complicated where statement to filter out those results added 40ms. Another example is .each() for arrays. It was faster to cycle through the array with other functions to make sure it contained the needed result, versus letting .each() process empty data.
• Cast as different types. I had a simple "where real_column > 1000" query. Changing it to "where real_column::float > 1000" shaved 20ms off the planner. TEXT IS NULL also benefited from TEXT::varchar IS NULL. It's possible this is just a good hack to help the planner avoid certain indexes.
• Finally, as Laurenz Albe helpfully pointed out, be careful with indexes. Sometimes the weight of the planner going through similar indexes outweighs the execution. Unfortunately this is made worse by the inability for the planner to match queries that are similar to a single index that encompasses both.