[Tile-serving] [osm2pgsql-dev/osm2pgsql] Use tags = NULL in middle tables if object doesn't have any tags (PR #2099)

[Jochen Topf]

This doesn't make much of a difference for the ways and rels table, but if we store all nodes in the database, it does make a huge difference, because most nodes don't have any tags. For a current planet, disk usage for the nodes table goes from 476 GB down to 409 GB saving 67 GB or nearly 15%.

Additionally it makes use of that table simpler. If you want to do any queries on tags, you need an index on the tags column on the nodes/ways/rels tables like this:

`CREATE INDEX ON planet_osm_ways USING gin (tags);`

But that is wasteful, because of the empty tags. We probably want to generate them as

`CREATE INDEX ON planet_osm_ways USING gin (tags) WHERE tags != '{}'::jsonb;`

But now all queries on those tables have to include that extra condition so that the query planner will use the index.

`SELECT * FROM planet_osm_ways WHERE tags ? 'highway' AND tags != '{}'::jsonb;`

If we use NULLs, the index can be created as:

`CREATE INDEX ON planet_osm_ways USING gin (tags) WHERE tags IS NOT NULL;`

And now the query becomes simpler, because the NOT NULL is automatically taken into account by the query planner:

`SELECT * FROM planet_osm_ways WHERE tags ? 'highway';`

Note that this is an incompatible change to the new format middle tables, but they are still marked as experimental, so we can do this.

This PR also contains a second commit for future proofing the members list of the rels middle table in case we want to do a similar change for that column in the future.
You can view, comment on, or merge this pull request online at:

  https://github.com/osm2pgsql-dev/osm2pgsql/pull/2099

-- Commit Summary --

  * Use tags = NULL in middle tables if object doesn't have any tags
  * Allow NULL values in new middle format members list

-- File Changes --

    M src/middle-pgsql.cpp (18)

-- Patch Links --

https://github.com/osm2pgsql-dev/osm2pgsql/pull/2099.patch
https://github.com/osm2pgsql-dev/osm2pgsql/pull/2099.diff

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/pull/2099
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/pull/2099 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231028/9bef8bc0/attachment.htm>