diff options
| author | Even Rouault <even.rouault@spatialys.com> | 2020-02-05 17:59:55 +0100 |
|---|---|---|
| committer | Even Rouault <even.rouault@spatialys.com> | 2020-02-05 18:54:04 +0100 |
| commit | aa2545bebddec14caf4bae8e7615dbb880613faa (patch) | |
| tree | b32b15cf45c9fa1ff5e76c862ee65d1045230364 /data/sql/proj_db_table_defs.sql | |
| parent | c077e5b8b19a7c376bec1a68c2d20334236aed09 (diff) | |
| download | PROJ-aa2545bebddec14caf4bae8e7615dbb880613faa.tar.gz PROJ-aa2545bebddec14caf4bae8e7615dbb880613faa.zip | |
Fix performance issue, affecting projinfo EPSG:7842
Fixes #1913
AuthorityFactory::createBetweenGeodeticCRSWithDatumBasedIntermediates() issued
a complex SQL query that pushes the SQLite3 query plan optimizer to its limits.
Was working reasonably with sqlite 3.11, but not with later versions.
So put less constraints in the main query and do post-processing checks and
auxiliary requests to avoid such issues.
For some unknown reason, this slightly slows down a bit execution time of the
whole test_cpp_api binary (~ 10%), but couldn't come with something better,
despite trying many variations of the main SQL query. It seems that in the
general case the non-filter LEFT JOIN on the supersession table helped,
except on this EPSG:7842 case.
Diffstat (limited to 'data/sql/proj_db_table_defs.sql')
| -rw-r--r-- | data/sql/proj_db_table_defs.sql | 2 |
1 files changed, 2 insertions, 0 deletions
diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql index b820b207..f34e1799 100644 --- a/data/sql/proj_db_table_defs.sql +++ b/data/sql/proj_db_table_defs.sql @@ -1367,6 +1367,8 @@ CREATE TABLE supersession( source TEXT ); +CREATE INDEX idx_supersession ON supersession(superseded_table_name, superseded_auth_name, superseded_code); + CREATE TRIGGER supersession_insert_trigger BEFORE INSERT ON supersession FOR EACH ROW BEGIN |