OHM Postgres DB

This documentation aims to monitor the performance of the PostgreSQL API-web database. As the number of users in OHM has been growing, the configurations need to be updated according to the demand or to support the growing user base.

We are going to execute one of the most expensive queries, which has been running on cgimap, and obtain the current response from the database.

• Query to execute: 👇

    SELECT relation_id FROM relations LIMIT 25;
    # 265,270,297,306,311,312,1080,1123,2287,2756,2771,2909,2913,2970,2972,2973,2988,2998,3001,3008,3084,3098,3101,3103,3105
    \o json
    EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
    SELECT MIN(cn.latitude) AS minlat,
           MIN(cn.longitude) AS minlon,
           MAX(cn.latitude) AS maxlat,
           MAX(cn.longitude) AS maxlon
    FROM current_nodes cn
    INNER JOIN current_way_nodes wn ON cn.id = wn.node_id
    INNER JOIN current_ways w ON wn.way_id = w.id
    INNER JOIN current_relation_members ON current_relation_members.member_id = w.id
    WHERE current_relation_members.member_type = 'Way'
      AND current_relation_members.relation_id = ANY('{265,270,297,306,311,312,1080,1123,2287,2756,2771,2909,2913,2970,2972,2973,2988,2998,3001,3008,3084,3098,3101,3103,3105}'::int[]);

Results at 2023/11/17

• https://explain.dalibo.com/plan/95df8af46c5148ee
• https://gist.github.com/Rub21/198cff84e7937dd5a56e91bdcccec99f

Results at 2023/29/17 after setting up a new configuration

• https://explain.dalibo.com/plan/f2f91gcd885a1d9h
• https://gist.github.com/Rub21/20444c089865c3219cecdb3fe2af09c8

TODO: Explain ☝️ the performance results

Any question , about the DB to @batpad or @rub21