[openstreetmap/openstreetmap-website] Backfill enhanced changeset stats (PR #6347)

[Tom Hughes]

tomhughes left a comment (openstreetmap/openstreetmap-website#6347)

I've managed to avoid the need for multiple nested aggregations, which tests suggest is quite a bit more efficient, by doing this:

```sql
WITH total AS (
  SELECT
    changesets.id AS changeset_id,
    SUM(CASE WHEN nodes.version = 1 THEN 1 ELSE 0 END) AS num_created_nodes,
    SUM(CASE WHEN nodes.version > 1 AND nodes.visible THEN 1 ELSE 0 END) AS num_modified_nodes,
    SUM(CASE WHEN nodes.version > 1 AND NOT nodes.visible THEN 1 ELSE 0 END) AS num_deleted_nodes,
    SUM(CASE WHEN ways.version = 1 THEN 1 ELSE 0 END) AS num_created_ways,
    SUM(CASE WHEN ways.version > 1 AND ways.visible THEN 1 ELSE 0 END) AS num_modified_ways,
    SUM(CASE WHEN ways.version > 1 AND NOT ways.visible THEN 1 ELSE 0 END) AS num_deleted_ways,
    SUM(CASE WHEN relations.version = 1 THEN 1 ELSE 0 END) AS num_created_relations,
    SUM(CASE WHEN relations.version > 1 AND relations.visible THEN 1 ELSE 0 END) AS num_modified_relations,
    SUM(CASE WHEN relations.version > 1 AND NOT relations.visible THEN 1 ELSE 0 END) AS num_deleted_relations
  FROM changesets
  LEFT OUTER JOIN nodes ON nodes.changeset_id = changesets.id
  LEFT OUTER JOIN ways ON ways.changeset_id = changesets.id
  LEFT OUTER JOIN relations ON relations.changeset_id = changesets.id
  WHERE changesets.id = ANY($1::bigint[])
  GROUP BY changesets.id
)
UPDATE changesets
SET num_created_nodes      = total.num_created_nodes,
    num_modified_nodes     = total.num_modified_nodes,
    num_deleted_nodes      = total.num_deleted_nodes,
    num_created_ways       = total.num_created_ways,
    num_modified_ways      = total.num_modified_ways,
    num_deleted_ways       = total.num_deleted_ways,
    num_created_relations  = total.num_created_relations,
    num_modified_relations = total.num_modified_relations,
    num_deleted_relations  = total.num_deleted_relations
FROM total
WHERE changesets.id = total.changeset_id;
```

I also got rid of the renaming of the CTE in the update but it didn't seem to serve any purpose.

In principle the CTE shouldn't need to access `changesets` but when I tried to replace that with an `unnest` of the array postgres decided to table scan `relations` instead of using the index which was very bad.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/openstreetmap-website/pull/6347#issuecomment-3225456094
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/openstreetmap-website/pull/6347/c3225456094 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/rails-dev/attachments/20250826/f809e494/attachment.htm>