First attempt to optimise sloq queries on tradeview country page
First attempt to optimise sloq queries on tradeview country page

vvvvv slack conversation explaining the thinking beyond this change vvvvvv

So, the ‘stripped down’ version of the bad query which still shows the slow query is something like:
WITH child_taxa AS (
 SELECT taxon_concept_id AS id
 FROM all_taxon_concepts_and_ancestors_mview
 WHERE ancestor_taxon_concept_id IN (32) -- Multiple taxa can be selected
 )
 SELECT
 term_id AS id from trade_plus_complete_mview
 WHERE exporter_id IN (80) -- @reported_by = importer if importing-from chart, exporter if exporting-to chart
 AND taxon_id IN ( SELECT DISTINCT(id) FROM child_taxa)

so we basically create a sub-query/temp table called child_taxa that has all the, well, child taxa in it - in this case we have ~5500 rows in this table which is a modest-case

and then for each row we have in the all_taxon_concepts_and_ancestors_mview (that matches exporter_id (assuming postgres applies this filter first) - in this case ~1m rows) we have to check the 5500 taxon_id rows WHICH AS A SUBQUERY DOESN’T HAVE AN INDEX

and this sucks - takes ~30s

switch it to a regular join/on:
SELECT
 term_id AS id from trade_plus_complete_mview
 WHERE exporter_id IN (80) -- @reported_by = importer if importing-from chart,
 JOIN all_taxon_concepts_and_ancestors_mview ON taxon_concept_id=taxon_id
exporter if exporting-to chart
 AND  ancestor_taxon_concept_id IN (32)
and boom - we are hitting indexes again and down to 200ms

happy days!

Except, why the heck did we do the WITH () stuff in the first place?

unepwcmc/SAPI,unepwcmc/SAPI,unepwcmc/SAPI,unepwcmc/SAPI