We've been using views in many queries and have noted a substantial performance regression from 3.38.5 to 3.39.0 (and up). I will post the following for reference:

1. The schema of the tables and views involved.
2. The query in question.
3. The query planner and timer output from 3.38.5
4. The query planner and timer output from 3.39.0
5. Workaround

1. Schema of tables and views

CREATE TABLE macro_transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created DATETIME NOT NULL DEFAULT(datetime('now')),
    completed DATETIME,
    type INTEGER NOT NULL,
    tag TEXT,
    source_id INTEGER,
    heartbeat DATETIME,
    checkpoint_id INTEGER,
    guid CHAR(36) NOT NULL
);

CREATE TABLE pypi_packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    cname TEXT NOT NULL,
    project_name TEXT NOT NULL,
    source_id INTEGER NOT NULL,
    summary TEXT
);

CREATE TABLE pypi_packages_macro_association (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    package_id INTEGER NOT NULL,
    version TEXT NOT NULL,
    json_path TEXT NOT NULL,
    tran_create INTEGER NOT NULL,
    tran_delete INTEGER,
    FOREIGN KEY (package_id) REFERENCES pypi_packages(id),
    FOREIGN KEY (tran_create) REFERENCES macro_transactions(id),
    FOREIGN KEY (tran_delete) REFERENCES macro_transactions(id)
);

CREATE INDEX pypi_packages_source_id_cname ON pypi_packages(source_id, cname);

CREATE INDEX pypi_packages_source_id_project_name ON pypi_packages(source_id, project_name);

CREATE INDEX pypi_packages_macro_association_package_id on pypi_packages_macro_association(package_id);

CREATE VIEW committed_macro_transactions AS
    SELECT m.*
    FROM macro_transactions m
    WHERE m.completed IS NOT NULL;

CREATE VIEW committed_pypi_packages_macro_association AS
    SELECT pa.*,
        rc.id AS committed_tran_create,
        rd.id AS committed_tran_delete
    FROM pypi_packages_macro_association pa
        -- Eliminates any newly created packages that have not been committed
        INNER JOIN committed_macro_transactions rc
            ON rc.id = pa.tran_create

        -- Helps determine if the package has been deleted
        LEFT JOIN committed_macro_transactions rd
            ON rd.id = pa.tran_delete;

2. The query in question.

SELECT p.id, p.source_id, p.cname, p.project_name,
    pa.version, pa.json_path, p.summary
FROM pypi_packages p
  INNER JOIN committed_pypi_packages_macro_association pa
    ON pa.package_id = p.id
WHERE p.cname='numpy'
  AND p.source_id=2
  AND pa.committed_tran_create <= 369
  AND (pa.committed_tran_delete is null
        OR pa.committed_tran_delete > 369
  );

3. The query planner and timer output from 3.38.5

sqlite> explain query plan select p.id, p.source_id, p.cname, p.project_name, pa.version, pa.json_path, p.summary from pypi_packages p inner join committed_pypi_packages_macro_association pa on pa.package_id = p.id where p.cname='numpy' and p.source_id=2 and pa.committed_tran_create <= 369 and (pa.committed_tran_delete is null or pa.committed_tran_delete > 369);

QUERY PLAN
|--SEARCH p USING INDEX pypi_packages_source_id_cname (source_id=? AND cname=?)
|--SEARCH pa USING INDEX pypi_packages_macro_association_package_id (package_id=?)
|--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)
`--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)

sqlite> .timer on
sqlite> select p.id, p.source_id, p.cname, p.project_name, pa.version, pa.json_path, p.summary from pypi_packages p inner join committed_pypi_packages_macro_association pa on pa.package_id = p.id where p.cname='numpy' and p.source_id=2 and pa.committed_tran_create <= 369 and (pa.committed_tran_delete is null or pa.committed_tran_delete > 369);

14207|2|numpy|numpy|1.23.3|numpy/1662989427.json|NumPy is the fundamental package for array computing with Python.
Run Time: real 0.000 user 0.000451 sys 0.000000

4. The query planner and timer output from 3.39.0

sqlite> explain query plan select p.id, p.source_id, p.cname, p.project_name, pa.version, pa.json_path, p.summary from pypi_packages p inner join committed_pypi_packages_macro_association pa on pa.package_id = p.id where p.cname='numpy' and p.source_id=2 and pa.committed_tran_create <= 369 and (pa.committed_tran_delete is null or pa.committed_tran_delete > 369);

QUERY PLAN
|--MATERIALIZE committed_pypi_packages_macro_association
|  |--SCAN pa
|  |--SEARCH m USING INTEGER PRIMARY KEY (rowid=?)
|  `--SEARCH m USING INTEGER PRIMARY KEY (rowid=?) LEFT-JOIN
|--SEARCH p USING INDEX pypi_packages_source_id_cname (source_id=? AND cname=?)
`--SEARCH pa USING AUTOMATIC PARTIAL COVERING INDEX (package_id=?)

sqlite> .timer on
sqlite> select p.id, p.source_id, p.cname, p.project_name, pa.version, pa.json_path, p.summary from pypi_packages p inner join committed_pypi_packages_macro_association pa on pa.package_id = p.id where p.cname='numpy' and p.source_id=2 and pa.committed_tran_create <= 369 and (pa.committed_tran_delete is null or pa.committed_tran_delete > 369);

14207|2|numpy|numpy|1.23.3|numpy/1662989427.json|NumPy is the fundamental package for array computing with Python.
Run Time: real 0.890 user 0.648108 sys 0.241639

Workaround

We've been able to work around the performance regression by eliminating the views altogether in favor of JOINs. I tried replacing the views with CTEs to no avail. Example:

SELECT p.id, p.source_id, p.cname, p.project_name,
  pa.version, pa.json_path, p.summary
FROM pypi_packages p
  INNER JOIN pypi_packages_macro_association pa
    ON pa.package_id = p.id
  INNER JOIN macro_transactions rc
    ON rc.id = pa.tran_create
      AND rc.completed is not null
  LEFT JOIN macro_transactions rd
    ON rd.id = pa.tran_delete 
      AND rd.completed is not null
WHERE p.cname='numpy' AND p.source_id=2
  AND pa.tran_create <= 369
  AND (pa.tran_delete is null OR pa.tran_delete > 369);

This modification performs well and has very similar query planner output to the 3.38.5 output from the original query. However, eliminating the view usage from ALL of our application queries will be difficult and cause considerable complication.

Is there any way to force SQLite to use the original behavior?

Thanks!