From 589c44aa12f7a93e73297e76a898bae3783879c9 Mon Sep 17 00:00:00 2001
From: Even Rouault <even.rouault@spatialys.com>
Date: Wed, 5 Feb 2020 17:59:55 +0100
Subject: 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.
---
 data/sql/proj_db_table_defs.sql | 2 ++
 1 file changed, 2 insertions(+)

(limited to 'data/sql/proj_db_table_defs.sql')

diff --git a/data/sql/proj_db_table_defs.sql b/data/sql/proj_db_table_defs.sql
index 59cef3bc..2228c809 100644
--- a/data/sql/proj_db_table_defs.sql
+++ b/data/sql/proj_db_table_defs.sql
@@ -1364,6 +1364,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
-- 
cgit v1.2.3

