importer: file-backed staging has no where:, so water_points stages 8M rows to read 4 921 #114

New issue

Open

opened 2026-08-13 05:16:01 +00:00 by art-bot · 0 comments

art-bot commented

2026-08-13 05:16:01 +00:00

Collaborator

Scope: importer/staging.go, the load: (file-backed) staging kind.

A file-backed staging table has no way to filter rows on the way in. stagingLoad
carries db / file / glob / layer / srs / source_srid / swap_axes /
promote_to_multi / gml_points / route_types / agencies / feature — and no
predicate. So a layer that needs a handful of rows out of a large source class has to
stage the whole class first, and filter in the layer query.

What was measured (live DB, after #85 landed):

staging	rows loaded	rows the layer reads	on-disk
`pl_oipr` (BDOT10k OT_OIPR_P)	8 020 907	4 921 (4 915 `źródło` + 6 `wodospad`)	2 730 MB

That is 1 usable row in 1 630, and 2.7 GB of table plus its GiST index carried
permanently so that water_points can draw 4 921 points. The rest of OIPR_P is trees
and scrub, which no layer reads and no layer is likely to read — the class is a
catch-all "obiekt przyrodniczy" bucket.

Why it cannot be worked around in config today: a query-kind staging entry with
needs: [pl_oipr] can narrow the table, but only after the full load has happened —
the cost being paid is the load and the intermediate table, not the final one. There is
no OGR-level attribute filter and no COPY-time predicate.

Shape of the fix: a where: on the load, applied per source file as the rows are
read (for the glob case, once per county package) rather than after the append. For
GeoPackage/OGR sources this can push down to the reader; worst case it is a row test
during the COPY append, which still avoids the table and the index.

Caveats worth deciding on:

The predicate is evaluated against source column names and types, before the SRS
reprojection and any promote/force — so it is a different namespace from the layer
query's. That needs to be stated in the docs or it will surprise someone.
It weakens the "staging is the source, faithfully" property that makes the staging
tables useful for ad-hoc catalogue checks — the R6 checks in #85 and #84 were both
run against staging tables precisely because they held everything. Possibly the rule
should be that where: is only for classes where the unread remainder is provably
irrelevant, and the comment has to say why.
pl_skpp is the counter-example that does not need this: 2 947 rows to yield 133,
a 1 MB table. The knob is for the order-of-magnitude cases, not every filter.

From the #85 review (Poland's water_points / ferries).

Scope: `importer/staging.go`, the `load:` (file-backed) staging kind. A file-backed staging table has no way to filter rows on the way in. `stagingLoad` carries `db` / `file` / `glob` / `layer` / `srs` / `source_srid` / `swap_axes` / `promote_to_multi` / `gml_points` / `route_types` / `agencies` / `feature` — and no predicate. So a layer that needs a handful of rows out of a large source class has to stage the whole class first, and filter in the layer query. **What was measured** (live DB, after #85 landed): | staging | rows loaded | rows the layer reads | on-disk | |---|---|---|---| | `pl_oipr` (BDOT10k OT_OIPR_P) | **8 020 907** | **4 921** (4 915 `źródło` + 6 `wodospad`) | **2 730 MB** | That is 1 usable row in 1 630, and 2.7 GB of table plus its GiST index carried permanently so that `water_points` can draw 4 921 points. The rest of OIPR_P is trees and scrub, which no layer reads and no layer is likely to read — the class is a catch-all "obiekt przyrodniczy" bucket. **Why it cannot be worked around in config today:** a query-kind staging entry with `needs: [pl_oipr]` can narrow the table, but only *after* the full load has happened — the cost being paid is the load and the intermediate table, not the final one. There is no OGR-level attribute filter and no COPY-time predicate. **Shape of the fix:** a `where:` on the load, applied per source file as the rows are read (for the glob case, once per county package) rather than after the append. For GeoPackage/OGR sources this can push down to the reader; worst case it is a row test during the COPY append, which still avoids the table and the index. **Caveats worth deciding on:** - The predicate is evaluated against *source* column names and types, before the SRS reprojection and any promote/force — so it is a different namespace from the layer query's. That needs to be stated in the docs or it will surprise someone. - It weakens the "staging is the source, faithfully" property that makes the staging tables useful for ad-hoc catalogue checks — the R6 checks in #85 and #84 were both run against staging tables precisely because they held everything. Possibly the rule should be that `where:` is only for classes where the unread remainder is provably irrelevant, and the comment has to say why. - `pl_skpp` is the counter-example that does **not** need this: 2 947 rows to yield 133, a 1 MB table. The knob is for the order-of-magnitude cases, not every filter. From the #85 review (Poland's `water_points` / `ferries`).

art-bot was assigned by art

2026-08-13 05:24:40 +00:00