0

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
  • Hmm. Your execution plan shows a planning time of 1 millisecond. How many indexes do you have on the table? – Laurenz Albe Sep 3 at 19:32
  • When I have an index on only id, way_area, and GIST(way) it takes 1 millisecond. When I had about 20 other GIST(way) indexes with specific filters it was 2ms on that query, and some other queries went from 3ms to 16-40ms. – Justin M Sep 3 at 20:57
0

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.

| improve this answer | |
  • Haven't messed with geqo_threshold, so that should be at default. I am currently redoing my indexes to be less query specific so that a single index covers a couple similar queries. I will look into prepared statements and report back if that fixes the issue. Thanks! – Justin M Sep 3 at 22:35
  • Just go with the single, simple index you already discovered to be good for all queries. – Laurenz Albe Sep 4 at 6:13
  • Well strangely enough the prepared statements had no effect on timing at all. I created the prepared statement and then hit run on the execute function a ton of times in HeidiSQL and it took on avg 3.3ms to plan and 0.9ms to execute, which was the same as running the query normally. I'm going to add my custom config to the original question in case that sheds any light. I have tried deleting any custom settings, and then doing a vacuum analyze on the table to no effect. Right now I am rebuilding the indexes with default config to see if that results in any solutions. – Justin M Sep 4 at 18:38
  • Also, while the simple index works great on queries that search over a small bounding box, I do require more specific indexes for some queries or the execute time is far greater than the planning time. – Justin M Sep 4 at 18:41
  • Reindexing with default config did not help either. For my query that was taking 3.3ms, stripping out a lot of indexes didn't help much until I stripped it down to a single index created for that query, which then lowered it to 2.7ms according to analyze. – Justin M Sep 4 at 21:46
0

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.
| improve this answer | |
  • If you say that you experience generally worse performance with v12, did you check if the JIT compiler might have kicked in unexpectedly? Sometimes jit = off is an improvement. – Laurenz Albe Sep 29 at 5:22
  • Yep. I do have jit = off, since it was indeed an improvement. Having it on did improve planning on a few queries, but drastically extended execution time on others. Openstreetmap and osm2pgsql has an issue with calculating statistics that makes jit and max_parallel_workers_per_gather cause problems when on. – Justin M Sep 29 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.