SQLite Forum

some interesting plan observations with dynamic SQL
Login

some interesting plan observations with dynamic SQL

(1) By Rico Mariani (rmariani) on 2021-07-21 18:42:21 [source]

I've been thinking about patterns to create dynamic SQL for predicates in some orderly fashion. In this case I was thinking of adding dynamic OR conditions. So I start with "0" and then add in ORs based on parameters. I noticed some things, I think some of this has been discussed earlier this week but I'll put it here for reference.

Here's what I ran with some notes. It's just an experiment. Based on this if I really wanted to do this I'd want to do some additional optimizations on the generated code so that for instance "OR 0" is removed.

select sqlite_version();
3.32.3

create table person( x text primary key, y text);
create table place( x text primary key, y text);

-- basic query, the control case, all works as expected

explain query plan select * from person left outer join place on person.x = place.x where person.x = 'x';
QUERY PLAN
|--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?)
`--SEARCH TABLE place USING INDEX sqlite_autoindex_place_1 (x=?)


-- suppose no condition was added to the left outer join and we 
-- were left with just the base 0
-- it would be nice if the query plan here did not include SCAN TABLE place

explain query plan select * from person left outer join place on 0 where person.x = 'x';
QUERY PLAN
|--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?)
`--SCAN TABLE place

-- note that adding 0 OR to the condition spoils the index selection
-- so that would have to be removed in the dynamic sql
explain query plan select * from person left outer join place on 0 or person.x = place.x where person.x = 'x';
QUERY PLAN
|--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?)
`--SCAN TABLE place

-- the order doesn't matter, it's still spoiled

explain query plan select * from person left outer join place on person.x = place.x or 0 where person.x = 'x';
QUERY PLAN
|--SEARCH TABLE person USING INDEX sqlite_autoindex_person_1 (x=?)
`--SCAN TABLE place

In case you're curious the metasyntax I was playing with looked like this:

create proc findstuff(place_ boolean)
begin
   -- Hypothetically, the OR clause is conditionally added to the generated sql based 
   -- on the place_ argument.  This is a silly example, you would only do this if the
   -- query was much more complicated and the predicate was just a small part of it.
   -- The general idea is that you might want conditional predicates that are dyamically
   -- geneated so that you get the best chance at a good plan.
   select * from 
     person
     left outer join place ON 0 /* start with nothing */ 
        OR IF (place_) place.x = person.x
   where 
     person.x = 'x';
end;