Postgresql – Slow planning time on PostgreSQL 12.4

indexpostgispostgresqlpostgresql-performancequery-performance

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

Best Answer

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.

Related Solutions

Postgresql – DataGrip shows a time substantially slower than EXPLAIN ANALYZE

EXPLAIN ANALYZE is showing you the planning and execution time.
DataGrip is showing you the completion time. This may include,
1. Network transit
2. Drawing in the GUI, etc.

Just think, if you have a one-row table with one bytea column that contains 2GB, transferring it across the network or even using TCP/IP will be a substantial transit compared to the planning and execution of the query.

[2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

That's 134 rows you didn't have to retrieve using EXPLAIN ANALYZE.

Postgresql – How to make DISTINCT ON faster in PostgreSQL

For only 400 stations, this query will be massively faster:

SELECT s.station_id, l.submitted_at, l.level_sensor
FROM   station s
CROSS  JOIN LATERAL (
   SELECT submitted_at, level_sensor
   FROM   station_logs
   WHERE  station_id = s.station_id
   ORDER  BY submitted_at DESC NULLS LAST
   LIMIT  1
   ) l;

dbfiddle here
_{(comparing plans for this query, Abelisto's alternative and your original)}

Resulting EXPLAIN ANALYZE as provided by the OP:

 Nested Loop  (cost=0.56..356.65 rows=102 width=20) (actual time=0.034..0.979 rows=98 loops=1)
   ->  Seq Scan on stations s  (cost=0.00..3.02 rows=102 width=4) (actual time=0.009..0.016 rows=102 loops=1)
   ->  Limit  (cost=0.56..3.45 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=102)
         ->  Index Scan using station_id__submitted_at on station_logs  (cost=0.56..664062.38 rows=230223 width=16) (actual time=0.009$
               Index Cond: (station_id = s.id)
 Planning time: 0.542 ms
 Execution time: 1.013 ms  -- !!

The only index you need is the one you created: station_id__submitted_at. The UNIQUE constraint uniq_sid_sat also does the job, basically. Maintaining both seems like a waste of disk space and write performance.

I added NULLS LAST to ORDER BY in the query because submitted_at isn't defined NOT NULL. Ideally, if applicable!, add a NOT NULL constraint to the column submitted_at, drop the additional index and remove NULLS LAST from the query.

If submitted_at can be NULL, create this UNIQUE index to replace both your current index and unique constraint:

CREATE UNIQUE INDEX station_logs_uni ON station_logs(station_id, submitted_at DESC NULLS LAST);

Consider:

This is assuming a separate table station with one row per relevant station_id (typically the PK) - which you should have either way. If you don't have it, create it. Again, very fast with this rCTE technique:

CREATE TABLE station AS
WITH RECURSIVE cte AS (
   (
   SELECT station_id
   FROM   station_logs
   ORDER  BY station_id
   LIMIT  1
   )
   UNION ALL
   SELECT l.station_id
   FROM   cte c
   ,      LATERAL (   
      SELECT station_id
      FROM   station_logs
      WHERE  station_id > c.station_id
      ORDER  BY station_id
      LIMIT  1
      ) l
   )
TABLE cte;

I use that in the fiddle as well. You could use a similar query to solve your task directly, without station table - if you can't be convinced to create it.

Detailed instructions, explanation and alternatives:

Optimize index

Your query should be very fast now. Only if you still need to optimize read performance ...

It might make sense to add level_sensor as last column to the index to allow index-only scans, like joanolo commented.
Con: It makes the index bigger - which adds a little cost to all queries using it.
Pro: If you actually get index only scans out of it, the query at hand does not have to visit heap pages at all, which makes it about twice as fast. But that may be an insubstantial gain for the very fast query now.

However, I don't expect that to work for your case. You mentioned:

... around 20k rows per day per station_id.

Typically, that would indicate unceasing write load (1 per station_id every 5 seconds). And you are interested in the latest row. Index-only scans only work for heap pages that are visible to all transactions (bit in the visibility map is set). You would have to run extremely aggressive VACUUM settings for the table to keep up with the write load, and it would still not work most of the time. If my assumptions are correct, index-only scans are out, don't add level_sensor to the index.

OTOH, if my assumptions hold, and your table is growing very big, a BRIN index might help. Related:

Speed up creation of Postgres partial index

Or, even more specialized and more efficient: A partial index for only the latest additions to cut off the bulk of irrelevant rows:

CREATE INDEX station_id__submitted_at_recent_idx ON station_logs(station_id, submitted_at DESC NULLS LAST)
WHERE submitted_at > '2017-06-24 00:00';

Chose a timestamp for which you know that younger rows must exist. You have to add a matching WHERE condition to all queries, like:

...
WHERE  station_id = s.station_id
AND    submitted_at > '2017-06-24 00:00'
...

You have to adapt index and query from time to time.
Related answers with more details:

Best Answer

Related Solutions

Postgresql – DataGrip shows a time substantially slower than EXPLAIN ANALYZE

Postgresql – How to make DISTINCT ON faster in PostgreSQL

Optimize index

Related Question