[PROJ] Performance when Auxilary DB used

Mon Aug 31 13:34:27 PDT 2026

Hey Even,

Removing the ORDER BY clause removed the need to materialize and gives the same query performance as with the temporary tables, but without the need to create any temporary tables which took extra time. Adding back just the "ORDER BY score" still avoided the materialize, it seemed to be the "usage.auth_name, usage.code" that triggered the materialize. Are those necessary, or is "ORDER BY score" sufficient?

Cheers,
Weston

________________________________
From: Even Rouault <even.rouault at spatialys.com>
Sent: Monday, August 31, 2026 6:43 PM
To: Weston Renoud <weston.renoud at qps.nl>; proj at lists.osgeo.org <proj at lists.osgeo.org>
Subject: Re: [PROJ] Performance when Auxilary DB used

You don't often get email from even.rouault at spatialys.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification>

Hi Weston,

thanks for this interesting analysis. I'm wondering if removing the ORDER BY clause would have a significant effect on the performance, if ordering would be the reason for materializing ? Sorting could be easily done on the C++ side. And the expected number of rows of the query in in 99% of the cases is just 1, and the remaining 1% 2, but SQLite has of course no clue about that.

Otherwise you could try to submit the question to the SQLite experts at https://sqlite.org/forum/forum, but to have some hope of them having a look at that, you should provide an easy reproducer using the sqlite3 binary and provide the databases likely stripped down to only contain the needed tables to facilitate their analysis.

Cheers,

Even

Le 31/08/2026 à 18:13, Weston Renoud via PROJ a écrit :
Hi all,

Even, Javier, and I discussed at FOSS4G EU some topics I could explore to get more familiar with PROJ and one of the ideas was to explore performance when using an auxiliary database. I have poked at the problem and wanted to share what I have found so far.

TL&DR I think I may have a found an improvement that reduces the auxillary database overhead by about 50%.

The diagnostics I'm using, and the improvement can be found in the branch https://github.com/OSGeo/PROJ/compare/master...wrenoud:PROJ:investigate-aux-db-performance

I focused on the usage of an empty auxiliary database with cs2cs, with a simple projection of WGS 84 / UTM zone 1N coordinates. In windows command prompt this looks like:

    set PROJ_AUX_DB=<path-to>/aux.db
    set PROJ_DEBUG=2
    cs2cs EPSG:4236 EPSG:32601 test.txt

I develop primarily on Windows and Linux for Intel CPUs, so I make use of Intel's Vtune Profiler when I'm looking to evaluate code performance. I instrumented the method `DatabaseContext::Private::run` with begin and end gates to time the SQL queries to try to identify if there are particular queries that perform especially poorly. What I found:

Usage of `DatabaseContext::Private::run`:
    without auxiliary database: 43 SQL queries, totaling ~3ms.
    with empty auxiliary database: 221 SQL queries, totaling ~70ms.

With the auxiliary database the extra queries and time could be broken down into 3 main categories
* Setup - about 20ms (97 queries) of the 70ms when using the auxiliary database is related to initial setup
* Duplicates - for queries that don't need cross referencing, they are called directly on each attached database and have comparable performance.
* Usage query - about 40-50ms (11 queries)

The setup does three main things: ATTACHING each database, querying the table structures, and creating temporary VIEWs to UNION the tables between the attached databases. These are required to support cross referencing from the auxiliary database(s) to items in the primary database, and it is not obvious there is any room for improvements.

The duplicates on each attached database are necessary given the additional/auxillary database. Again, it is not obvious there is any room for improvements here.

The usage query: of the queries on the UNION'ed VIEWs, the query from `AuthorityFactory::Private::createPropertiesSearchUsages` jumped out given the significant run time. Without an auxiliary database these queries sum to less than 0.1ms, but with an empty auxiliary database they sum to 40-50ms. This query requires a join between the usage, extent and scope tables.

    SELECT extent.description,
           extent.south_lat,
           extent.north_lat,
           extent.west_lon,
           extent.east_lon,
           scope.scope,
           (CASE WHEN scope.scope LIKE '%large scale%' THEN 0 ELSE 1 END) AS score
    FROM usage
             JOIN extent ON usage.extent_auth_name = extent.auth_name AND usage.extent_code = extent.code
             JOIN scope ON usage.scope_auth_name = scope.auth_name AND usage.scope_code = scope.code
    WHERE object_table_name = ?
      AND object_auth_name = ?
      AND object_code = ?
      AND NOT (usage.extent_auth_name = 'PROJ' AND usage.extent_code = 'EXTENT_UNKNOWN')
      AND NOT (usage.scope_auth_name = 'PROJ' AND usage.scope_code = 'SCOPE_UNKNOWN')
    ORDER BY score, usage.auth_name, usage.code

Trying to optimize this query seemed like a good target, so for the next step I tried to learn more about it by making use of `EXPLAIN QUERY PLAN` (https://sqlite.org/eqp.html).

Without the auxiliary database, the query execution is very straightforward and fast. It is executed as three searches by index or primary key:

    6,0,43,SEARCH usage USING INDEX idx_usage_object (object_table_name=? AND object_auth_name=? AND object_code=?)
    25,0,39,SEARCH extent USING PRIMARY KEY (auth_name=? AND code=?)
    31,0,34,SEARCH scope USING PRIMARY KEY (auth_name=? AND code=?)
    58,0,0,USE TEMP B-TREE FOR ORDER BY

With the auxiliary database the query plan bloats to this due to the JOINs over the UNION'ed tables:

    2,0,0,CO-ROUTINE usage
    3,2,0,COMPOUND QUERY
    4,3,0,LEFT-MOST SUBQUERY
    7,4,43,SEARCH db_0.usage USING INDEX idx_usage_object (object_table_name=? AND object_auth_name=? AND object_code=?)
    37,3,0,UNION ALL
    39,37,216,SCAN db_1.usage
    69,0,0,MATERIALIZE extent
    71,69,0,COMPOUND QUERY
    72,71,0,LEFT-MOST SUBQUERY
    74,72,135,SCAN db_0.extent
    93,71,0,UNION ALL
    95,93,215,SCAN db_1.extent
    117,0,0,MATERIALIZE scope
    119,117,0,COMPOUND QUERY
    120,119,0,LEFT-MOST SUBQUERY
    122,120,97,SCAN db_0.scope
    132,119,0,UNION ALL
    134,132,215,SCAN db_1.scope
    146,0,196,SCAN usage
    167,0,0,BLOOM FILTER ON extent (code=? AND auth_name=?)
    188,0,53,SEARCH extent USING AUTOMATIC COVERING INDEX (code=? AND auth_name=?)
    201,0,0,BLOOM FILTER ON scope (code=? AND auth_name=?)
    213,0,53,SEARCH scope USING AUTOMATIC COVERING INDEX (code=? AND auth_name=?)
    245,0,0,USE TEMP B-TREE FOR ORDER BY

Of note are SCAN and MATERIALIZE. When combined, they effectively duplicate the table's contents into temporary tables for the query.

Based on this I then explored if creating tables, instead of views for the extent and usage tables would improve the performance. This dropped the total query time (for all 11 calls) to ~3ms, with an overhead of ~4ms to create the tables. That is over 30ms reduction. But again, this is with an empty auxiliary database.

I will test further by putting some entries into the auxiliary tables and see if there is a significant difference. I'm also curious if anyone with more sqlite experience might have some ideas based on these findings? Or if there are other concerns or considerations I should be thinking about here?

Best Regards,
Weston

_______________________________________________
PROJ mailing list
PROJ at lists.osgeo.org<mailto:PROJ at lists.osgeo.org>
https://lists.osgeo.org/mailman/listinfo/proj

--
http://www.spatialys.com
My software is free, but my time generally not.
LLMs contribute to global warming and brain rot.
Let's guillotine them! "Ah ! ça ira, ça ira, ça ira !"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/proj/attachments/20260831/a04b2d23/attachment-0001.htm>