SQLite User Forum

Regression for complex CTE query from 3.45.2 to 3.47.2
Login
Chronological

Regression for complex CTE query from 3.45.2 to 3.47.2

(1) By Student (studentik) on 2025-01-17 18:35:17 [source]

Good day!

SQL version 3.45.2, query runs about 40 seconds.

SQL version 3.47.2, query hangs indefinitely.

SQL version 3.45.2 plan

3 0 0 |CO-ROUTINE (subquery-20)|
6 3 0 |CO-ROUTINE (subquery-21)|
9 6 0 |CO-ROUTINE (subquery-22)|
12 9 0 |MATERIALIZE search|
14 12 0 |SCAN CONSTANT ROW|
25 9 0 |MATERIALIZE gad|
27 25 0 |CO-ROUTINE gadpg|
33 27 0 |SCAN gadp USING COVERING INDEX IX_gadp_geographical_area_id|
69 25 0 |SCAN gadpg|
72 25 0 |SEARCH gad2 USING INDEX IX_gad_geographical_area_id (geographical_area_id=?)|
80 25 0 |SEARCH gadp USING INDEX IX_gadp_geographical_area_description_period_sid (geographical_area_description_period_sid=?)|
101 9 0 |MATERIALIZE megas|
108 101 0 |SCAN ega USING COVERING INDEX IX_ega_measure_sid|
144 9 0 |MATERIALIZE measure_countries|
147 144 0 |MATERIALIZE area_countries|
160 147 0 |SCAN ga2|
166 147 0 |SEARCH gam USING INDEX IX_gam_geographical_area_group_sid (geographical_area_group_sid=?)|
174 147 0 |SEARCH ga USING INDEX IX_ga_geographical_area_sid (geographical_area_sid=?)|
182 147 0 |SEARCH gad2 USING INDEX IX_gad_geographical_area_id (geographical_area_id=?)|
190 147 0 |USE TEMP B-TREE FOR GROUP BY|
243 144 0 |SCAN m USING INDEX IX_m_measure_sid|
248 144 0 |SEARCH ga USING INDEX IX_ga_geographical_area_sid (geographical_area_sid=?)|
260 144 0 |BLOOM FILTER ON ac (area=?)|
270 144 0 |SEARCH ac USING AUTOMATIC COVERING INDEX (area=?)|
276 144 0 |SEARCH ega USING INDEX IX_ega_measure_sid (measure_sid=? AND excluded_geographical_area=?) LEFT-JOIN|
333 9 0 |MATERIALIZE fdp|
336 333 0 |CO-ROUTINE (subquery-23)|
344 336 0 |SCAN fdp USING INDEX IX_fdp_footnote_id|
375 336 0 |USE TEMP B-TREE FOR ORDER BY|
393 333 0 |SCAN (subquery-23)|
513 9 0 |SCAN search|
515 9 0 |SCAN m|
544 9 0 |CORRELATED LIST SUBQUERY 18|
547 544 0 |MATERIALIZE cncode_parents|
552 547 0 |MATERIALIZE t|
555 552 0 |COMPOUND QUERY|
556 555 0 |LEFT-MOST SUBQUERY|
557 556 0 |SCAN CONSTANT ROW|
562 555 0 |UNION USING TEMP B-TREE|
563 562 0 |SCAN CONSTANT ROW|
568 555 0 |UNION USING TEMP B-TREE|
569 568 0 |SCAN CONSTANT ROW|
574 555 0 |UNION USING TEMP B-TREE|
575 574 0 |SCAN CONSTANT ROW|
580 555 0 |UNION USING TEMP B-TREE|
581 580 0 |SCAN CONSTANT ROW|
596 547 0 |SCAN s|
600 547 0 |SCAN t|
619 544 0 |BLOOM FILTER ON cncode_parents (cncode=?)|
629 544 0 |SEARCH cncode_parents USING AUTOMATIC COVERING INDEX (cncode=?)|
643 9 0 |SEARCH rrtd USING INDEX IX_rrtd_measure_generating_regulation_role (regulation_role_type_id=? AND language_id=?) LEFT-JOIN|
652 9 0 |SEARCH mcond USING INDEX IX_mcond_measure_sid (measure_sid=?) LEFT-JOIN|
660 9 0 |SEARCH ma USING COVERING INDEX IX_ma_action_code (action_code=?) LEFT-JOIN|
667 9 0 |SEARCH mad USING INDEX IX_mad_measure_action_description (action_code=? AND language_id=?) LEFT-JOIN|
676 9 0 |SEARCH mc USING INDEX IX_mc_measure_sid (measure_sid=?) LEFT-JOIN|
710 9 0 |SEARCH mcc USING INDEX IX_mcc_measure_condition_sid (measure_condition_sid=?) LEFT-JOIN|
718 9 0 |SEARCH mt USING INDEX IX_mt_measure_type_id (measure_type_id=?) LEFT-JOIN|
726 9 0 |SEARCH mtd USING INDEX IX_mtd_measure_type_id (measure_type_id=? AND language_id=?) LEFT-JOIN|
735 9 0 |SEARCH mts USING COVERING INDEX IX_mts_measure_type_series_id (measure_type_series_id=?) LEFT-JOIN|
742 9 0 |SEARCH mtsd USING INDEX IX_mtsd_measure_type_series_id (measure_type_series_id=? AND language_id=?) LEFT-JOIN|
751 9 0 |SEARCH ga USING INDEX IX_ga_geographical_area_sid (geographical_area_sid=?) LEFT-JOIN|
765 9 0 |BLOOM FILTER ON gad (geographical_area_id=?)|
775 9 0 |SEARCH gad USING AUTOMATIC COVERING INDEX (geographical_area_id=?) LEFT-JOIN|
783 9 0 |SEARCH mega USING INDEX IX_ega_measure_sid (measure_sid=? AND excluded_geographical_area=?) LEFT-JOIN|
801 9 0 |CORRELATED LIST SUBQUERY 17|
804 801 0 |MATERIALIZE country_areas|
806 804 0 |COMPOUND QUERY|
807 806 0 |LEFT-MOST SUBQUERY|
810 807 0 |SCAN search|
820 806 0 |UNION ALL|
825 820 0 |SCAN search|
829 820 0 |BLOOM FILTER ON ac (country=?)|
839 820 0 |SEARCH ac USING AUTOMATIC COVERING INDEX (country=?)|
857 801 0 |BLOOM FILTER ON country_areas (country=?)|
867 801 0 |SEARCH country_areas USING AUTOMATIC COVERING INDEX (country=?)|
887 9 0 |BLOOM FILTER ON megas (measure_sid=?)|
897 9 0 |SEARCH megas USING AUTOMATIC COVERING INDEX (measure_sid=?) LEFT-JOIN|
909 9 0 |BLOOM FILTER ON measure_countries (measure_sid=?)|
919 9 0 |SEARCH measure_countries USING AUTOMATIC COVERING INDEX (measure_sid=?) LEFT-JOIN|
927 9 0 |SEARCH de USING INDEX IX_de_duty_expression_id (duty_expression_id=?) LEFT-JOIN|
935 9 0 |SEARCH ded USING INDEX IX_ded_duty_expression_id (duty_expression_id=? AND language_id=?) LEFT-JOIN|
944 9 0 |SEARCH ac USING INDEX IX_ac_additional_code_sid (additional_code_sid=?) LEFT-JOIN|
952 9 0 |SEARCH acd USING INDEX IX_acd_additional_code_sid (additional_code_sid=? AND language_id=?) LEFT-JOIN|
961 9 0 |SEARCH actd USING INDEX IX_actd_additional_code_type_id (additional_code_type_id=? AND language_id=?) LEFT-JOIN|
970 9 0 |SEARCH fam USING INDEX IX_fam_measure_sid (measure_sid=?) LEFT-JOIN|
978 9 0 |SEARCH ftd USING INDEX IX_ftd_footnote_type_id (footnote_type_id=? AND language_id=?) LEFT-JOIN|
991 9 0 |BLOOM FILTER ON fdp (footnote_id=?)|
1001 9 0 |SEARCH fdp USING AUTOMATIC COVERING INDEX (footnote_id=?) LEFT-JOIN|
1009 9 0 |SEARCH fd USING INDEX IX_fd_footnote_id (footnote_id=? AND language_id=?) LEFT-JOIN|
1021 9 0 |SEARCH fagn USING INDEX IX_fagn_footnote_id (footnote_id=?) LEFT-JOIN|
1032 9 0 |SEARCH nfoot USING COVERING INDEX IX_n_goods_nomenclature_item_id (goods_nomenclature_item_id=? AND producline_suffix=?) LEFT-JOIN|
1208 9 0 |USE TEMP B-TREE FOR ORDER BY|
1261 6 0 |SCAN (subquery-22)|
1378 6 0 |USE TEMP B-TREE FOR ORDER BY|
1433 3 0 |SCAN (subquery-21)|
1574 3 0 |USE TEMP B-TREE FOR ORDER BY|
1627 0 0 |SCAN (subquery-20)|
1765 0 0 |USE TEMP B-TREE FOR ORDER BY|

SQL version 3.47.2 plan

3 0 0 |CO-ROUTINE (subquery-20)|
6 3 0 |CO-ROUTINE (subquery-21)|
9 6 0 |CO-ROUTINE (subquery-22)|
12 9 0 |MATERIALIZE search|
14 12 0 |SCAN CONSTANT ROW|
25 9 0 |MATERIALIZE gad|
27 25 0 |CO-ROUTINE gadpg|
33 27 212 |SCAN gadp USING COVERING INDEX IX_gadp_geographical_area_id|
69 25 82 |SCAN gadpg|
72 25 61 |SEARCH gad2 USING INDEX IX_gad_geographical_area_id (geographical_area_id=?)|
80 25 61 |SEARCH gadp USING INDEX IX_gadp_geographical_area_description_period_sid (geographical_area_description_period_sid=?)|
101 9 0 |MATERIALIZE megas|
108 101 213 |SCAN ega USING COVERING INDEX IX_ega_measure_sid|
144 9 0 |MATERIALIZE measure_countries|
147 144 0 |MATERIALIZE area_countries|
160 147 216 |SCAN ga2|
166 147 61 |SEARCH gam USING INDEX IX_gam_geographical_area_group_sid (geographical_area_group_sid=?)|
174 147 61 |SEARCH ga USING INDEX IX_ga_geographical_area_sid (geographical_area_sid=?)|
182 147 61 |SEARCH gad2 USING INDEX IX_gad_geographical_area_id (geographical_area_id=?)|
190 147 0 |USE TEMP B-TREE FOR GROUP BY|
243 144 222 |SCAN m USING INDEX IX_m_measure_sid|
248 144 61 |SEARCH ga USING INDEX IX_ga_geographical_area_sid (geographical_area_sid=?)|
260 144 0 |BLOOM FILTER ON ac (area=?)|
270 144 47 |SEARCH ac USING AUTOMATIC COVERING INDEX (area=?)|
276 144 61 |SEARCH ega USING INDEX IX_ega_measure_sid (measure_sid=? AND excluded_geographical_area=?) LEFT-JOIN|
333 9 0 |MATERIALIZE fdp|
336 333 0 |CO-ROUTINE (subquery-23)|
344 336 223 |SCAN fdp USING INDEX IX_fdp_footnote_id|
375 336 0 |USE TEMP B-TREE FOR ORDER BY|
393 333 82 |SCAN (subquery-23)|
513 9 -329 |SCAN search|
515 9 -129 |SCAN m|
544 9 0 |CORRELATED LIST SUBQUERY 18|
547 544 0 |MATERIALIZE cncode_parents|
552 547 0 |MATERIALIZE t|
555 552 0 |COMPOUND QUERY|
556 555 0 |LEFT-MOST SUBQUERY|
557 556 0 |SCAN CONSTANT ROW|
562 555 0 |UNION USING TEMP B-TREE|
563 562 0 |SCAN CONSTANT ROW|
568 555 0 |UNION USING TEMP B-TREE|
569 568 0 |SCAN CONSTANT ROW|
574 555 0 |UNION USING TEMP B-TREE|
575 574 0 |SCAN CONSTANT ROW|
580 555 0 |UNION USING TEMP B-TREE|
581 580 0 |SCAN CONSTANT ROW|
596 547 216 |SCAN s|
600 547 87 |SCAN t|
619 544 0 |BLOOM FILTER ON cncode_parents (cncode=?)|
629 544 56 |SEARCH cncode_parents USING AUTOMATIC COVERING INDEX (cncode=?)|
643 9 -269 |SEARCH rrtd USING INDEX IX_rrtd_measure_generating_regulation_role (regulation_role_type_id=? AND language_id=?) LEFT-JOIN|
652 9 -254 |SEARCH mcond USING INDEX IX_mcond_measure_sid (measure_sid=?) LEFT-JOIN|
660 9 -246 |SEARCH ma USING COVERING INDEX IX_ma_action_code (action_code=?) LEFT-JOIN|
667 9 -224 |SEARCH mad USING INDEX IX_mad_measure_action_description (action_code=? AND language_id=?) LEFT-JOIN|
676 9 -209 |SEARCH mc USING INDEX IX_mc_measure_sid (measure_sid=?) LEFT-JOIN|
710 9 -194 |SEARCH mcc USING INDEX IX_mcc_measure_condition_sid (measure_condition_sid=?) LEFT-JOIN|
718 9 -179 |SEARCH mt USING INDEX IX_mt_measure_type_id (measure_type_id=?) LEFT-JOIN|
726 9 -164 |SEARCH mtd USING INDEX IX_mtd_measure_type_id (measure_type_id=? AND language_id=?) LEFT-JOIN|
735 9 -156 |SEARCH mts USING COVERING INDEX IX_mts_measure_type_series_id (measure_type_series_id=?) LEFT-JOIN|
742 9 -134 |SEARCH mtsd USING INDEX IX_mtsd_measure_type_series_id (measure_type_series_id=? AND language_id=?) LEFT-JOIN|
751 9 -119 |SEARCH ga USING INDEX IX_ga_geographical_area_sid (geographical_area_sid=?) LEFT-JOIN|
763 9 -18 |SCAN gad LEFT-JOIN|
770 9 -89 |SEARCH mega USING INDEX IX_ega_measure_sid (measure_sid=? AND excluded_geographical_area=?) LEFT-JOIN|
788 9 0 |CORRELATED LIST SUBQUERY 17|
791 788 0 |MATERIALIZE country_areas|
793 791 0 |COMPOUND QUERY|
794 793 0 |LEFT-MOST SUBQUERY|
797 794 216 |SCAN search|
807 793 0 |UNION ALL|
812 807 216 |SCAN search|
816 807 0 |BLOOM FILTER ON ac (country=?)|
826 807 53 |SEARCH ac USING AUTOMATIC COVERING INDEX (country=?)|
844 788 0 |BLOOM FILTER ON country_areas (country=?)|
854 788 56 |SEARCH country_areas USING AUTOMATIC COVERING INDEX (country=?)|
872 9 -53 |SCAN megas LEFT-JOIN|
881 9 -38 |SCAN measure_countries LEFT-JOIN|
888 9 -44 |SEARCH de USING INDEX IX_de_duty_expression_id (duty_expression_id=?) LEFT-JOIN|
896 9 -29 |SEARCH ded USING INDEX IX_ded_duty_expression_id (duty_expression_id=? AND language_id=?) LEFT-JOIN|
905 9 -14 |SEARCH ac USING INDEX IX_ac_additional_code_sid (additional_code_sid=?) LEFT-JOIN|
913 9 1 |SEARCH acd USING INDEX IX_acd_additional_code_sid (additional_code_sid=? AND language_id=?) LEFT-JOIN|
922 9 16 |SEARCH actd USING INDEX IX_actd_additional_code_type_id (additional_code_type_id=? AND language_id=?) LEFT-JOIN|
931 9 32 |SEARCH fam USING INDEX IX_fam_measure_sid (measure_sid=?) LEFT-JOIN|
939 9 46 |SEARCH ftd USING INDEX IX_ftd_footnote_type_id (footnote_type_id=? AND language_id=?) LEFT-JOIN|
950 9 82 |SCAN fdp LEFT-JOIN|
957 9 61 |SEARCH fd USING INDEX IX_fd_footnote_id (footnote_id=? AND language_id=?) LEFT-JOIN|
969 9 61 |SEARCH fagn USING INDEX IX_fagn_footnote_id (footnote_id=?) LEFT-JOIN|
980 9 53 |SEARCH nfoot USING COVERING INDEX IX_n_goods_nomenclature_item_id (goods_nomenclature_item_id=? AND producline_suffix=?) LEFT-JOIN|
1156 9 0 |USE TEMP B-TREE FOR ORDER BY|
1209 6 336 |SCAN (subquery-22)|
1326 6 0 |USE TEMP B-TREE FOR ORDER BY|
1381 3 336 |SCAN (subquery-21)|
1522 3 0 |USE TEMP B-TREE FOR ORDER BY|
1575 0 336 |SCAN (subquery-20)|
1713 0 0 |USE TEMP B-TREE FOR ORDER BY|

Query:


WITH search AS
(
  SELECT
         NULL AS country
       , NULL cncode
       , 1 only_duties
       , NULL regulation

)
, cncode_parents AS MATERIALIZED
(
  SELECT s.cncode, SUBSTR(SUBSTR(s.cncode, 1, t.level) || '0000000000', 1, 10) cncode_parent, t.level
  FROM search s
  JOIN (SELECT 2 level UNION SELECT 4 UNION SELECT 6 UNION SELECT 8 UNION SELECT 10) t ON 1 = 1
)
, area_countries AS
(
    SELECT ga2.geographical_area_id area
          , TRIM(ga.geographical_area_id) country
    FROM geographical_area ga
    JOIN geographical_area_membership gam ON gam.geographical_area_sid = ga.geographical_area_sid AND gam.validity_end_date IS NULL
    JOIN geographical_area ga2 ON ga2.geographical_area_sid = gam.geographical_area_group_sid AND ga2.validity_end_date IS NULL
    JOIN geographical_area_description gad2 ON gad2.geographical_area_id = ga2.geographical_area_id AND gad2.language_id = 'EN'
    WHERE ga.validity_end_date IS NULL AND ga2.geographical_code = '1'
    GROUP BY ga2.geographical_area_id, ga.geographical_area_id
)
, country_areas AS -- country itself plus all areas where country is present
(
  SELECT search.country, SUBSTR(search.country || '    ', 1, 4) area
  FROM search
  UNION ALL
  SELECT search.country, ac.area
  FROM search
  JOIN area_countries ac ON ac.country = search.country
)
, measure_countries AS MATERIALIZED
(
  SELECT m.measure_sid, STRING_AGG(ac.country, ';') countries
  FROM main.measure m
  JOIN main.geographical_area ga ON ga.geographical_area_sid = m.geographical_area_sid AND ga.validity_end_date IS NULL
  JOIN area_countries ac ON ac.area = ga.geographical_area_id
  LEFT JOIN main.measure_excluded_geographical_area ega ON ega.measure_sid = m.measure_sid AND ega.excluded_geographical_area = SUBSTR(ac.country || '    ', 1, 4)
  WHERE ega.rn IS NULL AND m.validity_end_date IS NULL
--     AND 1=0 --speedup query
  GROUP BY m.measure_sid
)
, megas AS MATERIALIZED
(
  SELECT ega.measure_sid, STRING_AGG(TRIM(ega.excluded_geographical_area), ';') excluding_countries
  FROM main.measure_excluded_geographical_area ega
  GROUP BY ega.measure_sid
)
SELECT ROW_NUMBER() OVER (ORDER BY search.cncode, search.country, m.goods_nomenclature_item_id, m.measure_type, mc.duty_amount DESC) rn
--      , search.cncode search_cncode
--      , search.country search_country
     , m.measure_sid --2414732, 2749070
     , m.validity_start_date --2022-11-05
     , m.validity_end_date --2025-06-30
     , SUBSTR(m.measure_generating_regulation_id, 1, 1) || RTRIM(SUBSTR(m.measure_generating_regulation_id, 4, 5), '0') || '/' || SUBSTR(m.measure_generating_regulation_id, 2, 2) regulation --R09660/23
     , m.goods_nomenclature_item_id cncode
--      , m.measure_generating_regulation_id --R9613470, R9810530
     , m.measure_generating_regulation_role || ' - ' || rrtd.description regulation_role --1 - Base regulation, Explicit abrogation
     , TRIM(ga.geographical_area_id) || ' - ' || gad.description area --1011 - ERGA OMNES, China, Russian Federation, European Union
     , megas.excluding_countries
     , IFNULL(measure_countries.countries, TRIM(ga.geographical_area_id)) including_countries
     , TRIM(mt.measure_type_series_id) || TRIM(m.measure_type) || ' - ' || mtd.description measure_type --552 - Definitive anti-dumping duty
     , mtsd.description type_series_description --Applicable duty, Entry into free circulation or exportation subject to conditions
     , TRIM(ma.action_code) || ' - ' || mad.description action --27 - Apply the mentioned duty, --Import/export allowed after control
     , mcond.certificate_type_code || mcond.certificate_code  action_certificate
     , ded.description duty_description --% or amount
     , LAST_VALUE(m.measure_sid) OVER (PARTITION BY IFNULL(search.cncode, m.goods_nomenclature_item_id), IFNULL(search.country, ga.geographical_area_id) ORDER BY m.measure_type, mc.duty_amount RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
       measure_sid_best --for every cncode/country, measure with the highest duty amount among measures of most relevant measure type (C142 > C106 > C103)
     , mc.duty_amount --0.000, 30.600
     , mc.monetary_unit_code --EUR
     , mcc.duty_amount mcc_duty_amount --0.000
     , de.duty_amount_applicability_code
     , de.measurement_unit_applicability_code
     , ac.additional_code
     , actd.description additional_code_type_description
     , acd.description additional_code_description
     , TRIM(fam.footnote_type_id) || TRIM(fam.footnote_id) || ' - ' || ftd.description footnote --TN701 - Taric Nomenclature, CD333 - Conditions
     , fd.description footnote_description
     , nfoot.goods_nomenclature_item_id footnote_cncode
FROM search
JOIN measure m ON m.validity_end_date IS NULL OR m.validity_end_date > date('now')
LEFT JOIN regulation_role_type_description rrtd ON rrtd.regulation_role_type_id = m.measure_generating_regulation_role AND rrtd.language_id = 'EN'
LEFT JOIN main.measure_condition mcond ON mcond.measure_sid = m.measure_sid
LEFT JOIN main.measure_action ma ON ma.action_code = mcond.action_code
LEFT JOIN main.measure_action_description mad ON mad.action_code = ma.action_code AND mad.language_id = 'EN'
LEFT JOIN main.measure_component mc ON mc.measure_sid = m.measure_sid
LEFT JOIN main.measure_condition_component mcc ON mcc.measure_condition_sid = mcond.measure_condition_sid
LEFT JOIN main.measure_type mt ON mt.measure_type_id = m.measure_type
LEFT JOIN main.measure_type_description mtd ON mtd.measure_type_id = mt.measure_type_id AND mtd.language_id = 'EN'
LEFT JOIN main.measure_type_series mts ON mts.measure_type_series_id = mt.measure_type_series_id
LEFT JOIN main.measure_type_series_description mtsd ON mtsd.measure_type_series_id = mt.measure_type_series_id AND mtsd.language_id = 'EN'

LEFT JOIN geographical_area ga ON ga.geographical_area_sid = m.geographical_area_sid AND ga.validity_end_date IS NULL
LEFT JOIN (
  SELECT gad2.geographical_area_id
       , gad2.description
       , gadp.validity_start_date
  FROM geographical_area_description gad2
  JOIN (SELECT gadp.geographical_area_id, MAX(gadp.validity_start_date) validity_start_date FROM geographical_area_description_period gadp GROUP BY gadp.geographical_area_id) gadpg ON gadpg.geographical_area_id = gad2.geographical_area_id
  JOIN (SELECT gadp.geographical_area_id, gadp.geographical_area_description_period_sid, gadp.validity_start_date FROM geographical_area_description_period gadp) gadp ON  gadp.geographical_area_description_period_sid = gad2.geographical_area_description_period_sid AND gadp.validity_start_date = gadpg.validity_start_date
  WHERE gad2.language_id = 'EN'
) gad ON gad.geographical_area_id = ga.geographical_area_id
LEFT JOIN main.measure_excluded_geographical_area mega ON mega.measure_sid = m.measure_sid AND mega.excluded_geographical_area = SUBSTR(search.country || '    ', 1, 4)
LEFT JOIN megas ON megas.measure_sid = m.measure_sid
LEFT JOIN measure_countries ON measure_countries.measure_sid = m.measure_sid

LEFT JOIN main.duty_expression de ON de.duty_expression_id = mc.duty_expression_id
LEFT JOIN duty_expression_description ded ON ded.duty_expression_id = de.duty_expression_id AND ded.language_id = 'EN'
LEFT JOIN main.additional_code ac ON ac.additional_code_sid = m.additional_code_sid
LEFT JOIN main.additional_code_description acd ON acd.additional_code_sid = ac.additional_code_sid AND acd.language_id = 'EN'
LEFT JOIN main.additional_code_type_description actd ON actd.additional_code_type_id = ac.additional_code_type_id AND actd.language_id = 'EN'

LEFT JOIN main.footnote_association_measure fam ON fam.measure_sid = m.measure_sid
LEFT JOIN main.footnote_type_description ftd ON ftd.footnote_type_id = fam.footnote_type_id AND ftd.language_id = 'EN'
LEFT JOIN
(
  SELECT fdp.footnote_id, LAST_VALUE(fdp.footnote_description_period_sid) OVER (PARTITION BY fdp.footnote_id ORDER BY fdp.validity_start_date) footnote_description_period_sid_latest
  FROM main.footnote_description_period fdp
  GROUP BY fdp.footnote_id
) fdp ON fdp.footnote_id = fam.footnote_id
LEFT JOIN main.footnote_description fd ON fd.footnote_id = fdp.footnote_id AND fd.language_id = 'EN' AND fd.footnote_description_period_sid = fdp.footnote_description_period_sid_latest
LEFT JOIN main.footnote_association_goods_nomenclature fagn ON fagn.footnote_id = fam.footnote_id AND fagn.goods_nomenclature_item_id = m.goods_nomenclature_item_id
LEFT JOIN main.goods_nomenclature nfoot ON nfoot.goods_nomenclature_item_id = fagn.goods_nomenclature_item_id AND nfoot.producline_suffix = fagn.productline_suffix

WHERE 1=1
  AND (search.country IS NULL
    OR (
       ga.geographical_area_id IN (SELECT area FROM country_areas WHERE country_areas.country = search.country)
       AND mega.rn IS NULL -- searched country is not present in measure excluded areas
    )
  )

  AND (search.regulation IS NULL
          -- convert R0966/23 to R2309660
         OR m.measure_generating_regulation_id = SUBSTR(search.regulation, 1, 1) || SUBSTR(search.regulation, -2, 2) || SUBSTR((SUBSTR(search.regulation, 2, INSTR('/', search.regulation)-2) || '00000'), 1, 5)
  )

  AND (search.only_duties = 0
    OR (
      mc.duty_amount IS NOT NULL --duty amount present
      AND mcond.rn IS NULL --no action certificates
      AND mc.monetary_unit_code IS NULL --only non-currency duties
      AND m.measure_type IN ('103   ', '106   ', '142   ') --only C103 - Third country duty, C106 - Customs Union Duty, C142 - Tariff preference
    )
  )

  AND (search.cncode IS NULL OR m.goods_nomenclature_item_id IN (SELECT cncode_parent FROM cncode_parents WHERE search.cncode = cncode_parents.cncode))
ORDER BY rn

(2) By Richard Hipp (drh) on 2025-01-17 20:41:24 in reply to 1 [link] [source]

Status Report:

  • The OP sent me his 645MB database via a private channel.
  • The test query uses 53 different tables and indexes (out of 121 in the total schema).
  • I was able to simplify the problem down to a 483-line script.
  • The problem bisects to check-in 38db9b5c83dfb302.
  • The difference in the query plans between the fast and slow query is that the fast query uses 8 automatic indexes whereas the slow variant only uses 4. The missing 4 automatic indexes get converted into full table scans, which explains the slowdown.

No solution yet, but this seems solvable.

(3.1) Originally by Mike Castle (nexushoratio) with edits by Richard Hipp (drh) on 2025-01-17 23:54:43 from 3.0 in reply to 2 [link] [source]

Out of curiosity, does this seem related to the various other recent slowdowns mentioned? Particularly, your comment at https://sqlite.org/forum/forumpost/454c375981

(4.1) By Richard Hipp (drh) on 2025-01-17 23:02:04 edited from 4.0 in reply to 3.0 [link] [source]

Not that I can tell. This appears to be an entirely new problem. Maybe after I fix this problem, though, if one or more of the other problems go away, then I'll be proven wrong.

(6) By Student (studentik) on 2025-01-18 17:13:47 in reply to 2 [link] [source]

Wow, this is blazing fast, thank you!

(5) By Richard Hipp (drh) on 2025-01-17 23:54:03 in reply to 1 [link] [source]

Please try again using check-in 0852c57ee2768224 or later. I believe that check-in will clear your problem. Correct me if I'm wrong.