Hi, I've noticed that since I upgraded to a newer version of sqlite at work, that complex queries with several common table expressions have become significantly slower.

At first I thought this might be a python issue since I didn't run into any issues on my private laptop.

But now I also ran into similar issues at home, when I had a view which I created using the DB Browser for SQLite that was running very slowly when accessed through PHP. The issue persisted when using sqlite on the command line.

The sqlite versions of the different programs are as follows:

I downloaded and compiled previous versions of sqlite to figure out when the issue first appears. As far as I can tell the change happened between

The issue can be tested as follows:

 CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    activity TEXT,
    finished INTEGER
);

Adding random records:

WITH RECURSIVE 
    cnt(x) AS (
        SELECT 1
        UNION ALL
        SELECT x+1 FROM cnt 
        LIMIT 30000
    )
INSERT INTO events (activity, finished)
SELECT 
    (random() % 100),
    ABS(RANDOM() % 2)
FROM cnt;

The following query is a simplified version of the actual query I was using. The goal is not to improve this specific query, but to show that the same query executed on the same data using different sqlite versions has a significant performance difference:

with BASE as (
select e.*,
	CASE 
	    WHEN activity like '1%' THEN 'No. 1'
		ELSE activity
	END AS extracted_name
	from EVENTS e
), finished_books as (
select * from BASE
where finished = 1
), entries_same_activity_after_finished as (
select b.*, f.id as id_before from BASE b
inner join finished_books f
on b.extracted_name = f.extracted_name and b.id > f.id
), details_relevant_activities as (
select b.* from base b
inner join (select distinct extracted_name, id from entries_same_activity_after_finished) e
on b.extracted_name = e.extracted_name and b.id = e.id
)
select b.*, 
	b.extracted_name || 
			case when d.id is null then ''
				else 'D' end as extracted_name_dupl from BASE b
left join details_relevant_activities d
on b.id = d.id

System Information:

OS: Fedora release 40 (Forty) x86_64
Host: HP ProBook 450 G2 A3009DD10303
Kernel: 6.12.6-100.fc40.x86_64
CPU: Intel i5-4210U (4) @ 2.700GHz 

Timing comparison (for different versions and sizes of test set):

The query plans are as follows:

Version 3.40.1:

QUERY PLAN
|--MATERIALIZE BASE
|  `--SCAN e
|--MATERIALIZE details_relevant_activities
|  |--MATERIALIZE e
|  |  |--SCAN BASE
|  |  |--SEARCH b USING AUTOMATIC COVERING INDEX (extracted_name=?)
|  |  `--USE TEMP B-TREE FOR DISTINCT
|  |--SCAN b
|  `--SEARCH e USING AUTOMATIC COVERING INDEX (id=? AND extracted_name=?)
|--SCAN b
`--SEARCH d USING AUTOMATIC COVERING INDEX (id=?) LEFT-JOIN

Version 3.48.0:

QUERY PLAN
|--MATERIALIZE details_relevant_activities
|  |--CO-ROUTINE e
|  |  |--SCAN e
|  |  |--BLOOM FILTER ON e (finished=?)
|  |  `--SEARCH e USING AUTOMATIC PARTIAL COVERING INDEX (finished=?)
|  |--SCAN e
|  |--BLOOM FILTER ON e (extracted_name=? AND id=?)
|  `--SEARCH e USING AUTOMATIC COVERING INDEX (extracted_name=? AND id=?)
|--SCAN e
|--BLOOM FILTER ON d (id=?)
`--SEARCH d USING AUTOMATIC COVERING INDEX (id=?) LEFT-JOIN

Since I use sqlite to analyze data locally I'm used to write fairly complex SQL statements with many CTEs (usually accessing a lot of data). Currently my workaround for dealing with such long running queries is to generate tables for the intermediate steps.

I did find it rather surprising that this example query would run more than 3 minutes for 30'000 rows.

Can you investigate what the cause of this significant performance difference between the different sqlite versions is?

Maybe add the MATERIALIZED keyword to the "base" CTE?

with BASE as MATERIALIZED ( ...

This is fantastic, it clearly massively speeds up the query.

This looks like it could also fix the issues I'm having at work.

Thank you very much for this advice.

Thanks for providing an actual test case!

I might put up a new 3.48.0 beta later today with tweaks to the query planner so that SQLite can figure out to put in the MATERIALIZED there by itself, without you having to modify your query. Emphasis on "might". Analysis is on-going.

Having the query planner do it automatically would be very convenient.

But having learned about MATERIALIZED was already a great start for the new year.

I might put up a new 3.48.0 beta later today....

I'm thinking that "might" is now "probably not".

My idea was to force materialization of CTEs that are used more than once. The BASE CTE in the original query is used 4 separate times, so that would force it to be materialized, thus solving the problem.

But it turns out that SQLite used to do exactly that: force materialization for CTEs that are used more than once. But that caused other performance problems described at Forum post 8f38fc9878a92aa9 by Grunthos about 2 years ago. As things stand right now, I can one or the other of:

• Make Grunthos's query go fast
• Make Jean-Noël's query go fast

But so far, I have not come up with a way to do both at the same time.

Thanks for the update.

The linked forum post was pretty interesting. I guess that different people will run into different issues when using complex CTEs.

Given the performance issues you've outlined, it makes sense to leave it as is for now.

Knowing about the possibility to use MATERIALIZED I believe that I should be able to mitigate the issues I've been having so far.

Thank you again for looking into the issue.

Newbie question: How does one know when to use that "MATERIALIZED" keyword?

Never mind. Found it here.