SQLite Forum

Can this simple query be optimized?
Login
Your window function version has the following query plan:

    QUERY PLAN
    `--COMPOUND QUERY
       |--LEFT-MOST SUBQUERY
       |  |--CO-ROUTINE 1
       |  |  |--CO-ROUTINE 4
       |  |  |  |--SCAN TABLE foo
       |  |  |  `--USE TEMP B-TREE FOR ORDER BY
       |  |  `--SCAN SUBQUERY 4
       |  `--SCAN SUBQUERY 1
       `--UNION USING TEMP B-TREE
          |--CO-ROUTINE 1
          |  |--CO-ROUTINE 5
          |  |  |--SCAN TABLE foo
          |  |  `--USE TEMP B-TREE FOR ORDER BY
          |  `--SCAN SUBQUERY 5
          `--SCAN SUBQUERY 1

I don't know how much of a performance difference it makes in practice,
but the following query plan has fewer co-routines and subqueries:

    WITH M(x, y, rmin, rmax) AS (
        SELECT x, y,
               rank() OVER (PARTITION BY (X/10) ORDER BY y ASC),
               rank() OVER (PARTITION BY (X/10) ORDER BY y DESC)
          FROM foo
    )
    SELECT x,y
    FROM M
    WHERE rmin=1 OR rmax=1;

    --  QUERY PLAN
    --  |--CO-ROUTINE 1
    --  |  |--CO-ROUTINE 3
    --  |  |  |--CO-ROUTINE 4
    --  |  |  |  |--SCAN TABLE foo
    --  |  |  |  `--USE TEMP B-TREE FOR ORDER BY
    --  |  |  |--SCAN SUBQUERY 4
    --  |  |  `--USE TEMP B-TREE FOR ORDER BY
    --  |  `--SCAN SUBQUERY 3
    --  `--SCAN SUBQUERY 1