Performance Tuning, especially when counting deletions (the FIXME case)

[sfkeller]

The views on the database are sometimes slow with complex queries, especially when counting deletions - as in the "FIXME case" (counting removed FIXMEs).

Therefore, the PostgreSQL database must be specifically examined for performance bottlenecks by applying EXPLAIN ANALYZE and e.g. pgtune, type conversions (datetime) and read optimizations (e.g. CREATE UNLOGGED TABLE...).

Another hint (source: https://www.crunchydata.com/blog/performance-and-spatial-joins):

SHOW max_worker_processes;            -- 8?
SHOW max_parallel_workers;            -- 8?
SHOW max_parallel_workers_per_gather; -- 2?
SHOW min_parallel_table_scan_size;    -- 8MB?

Then try

SET max_parallel_workers_per_gather = 8;
SET min_parallel_table_scan_size = '1kB';

[lbuchli]

Work done:

• Use periodically refreshed materialized views for data storage, allow these updates to occur concurrently to database requests.
• Developement of a new, more accurate query specialized on counting deletions, based on a specially optimized version of the Gaps and Islands approach. Previuous inaccuracies allowed for a speedup, these inaccuracies have now been eliminated,. the accurate algorithm is not quite as fast yet as the previous inaccurate one, but orders of magnitude better than a previous straightforward accurate implementation.

[sfkeller]

Thanks @lbuchli

Use periodically refreshed materialized views for data storage, allow these updates to occur concurrently to database requests.

Resolved in https://gitlab.ost.ch/ifs/geometalab/potm/ (currently a private repo)

Developement of a new, more accurate query specialized on counting deletions

Ok. So let's leave this issue open until we really can cover this edge case.