Let's say I have a table like this:
>~~~
CREATE TABLE ListItem (
id INTEGER PRIMARY KEY
, list_id INTEGER REFERENCES List(id)
);
CREATE INDEX fk ON ListItem(list_id);
~~~
Let's get an item's position in its list:
>~~~
SELECT
ListItem.*
, row_number() OVER () AS position
FROM ListItem
WHERE list_id = 99;
~~~
This is fast, and its speed is dependent on the number of items in the
single list (not the total number of ListItems) since the WHERE clause
filters the input rows before they get to the window function.
Now let's generalize this into a view:
>~~~
CREATE VIEW ListItem_Annotated AS
SELECT
ListItem.*
, row_number() OVER (PARTITION BY list_id) AS position
FROM ListItem;
~~~
>~~~
SELECT *
FROM ListItem_Annotated
WHERE list_id = 99;
~~~
Unfortunately, the speed of this query is dependent on the total number
of ListItems across all Lists. It seems that the optimizer isn't using
the WHERE clause to filter the input rows before they get to the window
function, even though we know that we're partitioning on the same column
as is in the WHERE clause.
EXPLAIN QUERY PLAN confirms a less optimal query as well. In the first
case, we do a SEARCH TABLE using the `fk` index. However in the second
case, we do a SCAN TABLE instead, as well as an additional
CO-ROUTINE and SCAN SUBQUERY.
Is there some reason the optimizer can't turn these into the same query
plan?
Thanks!
-----
ps:
[Here's some SQL](https://pastebin.com/raw/gPHgH3Ug)
to create, populate, and test the above example.
pps:
Thanks for SQLite! It's really phenomenal. I'm consistently amazed by
what it can do, and inspired by the project's values. Cheers!