testing sqlite query speed with complex joins and rtree spatial filtering

sqlite-speedtest.sql

cd ~/github/atlas-project/
sqlite3 /usr/share/mapbox/project/compare-s-2008-cty-g-2008-cty/layers/elections.sqlite
attach database "tabular-data/comp_res.sqlite" as comp_res;

-- speed-test1
select count(*)
from attributes
join (
    select geoid as results_geoid, (cast(dem as real) / cast(total as real) * 100) as pct1, 
    	(
    		replace(sub_pct, cast(dem as real) / cast(total as real) * 100, "")
    	) as pct2
    from comp_res.results
    join (
        select level as sub_level, geoid as sub_geoid, 
            group_concat((cast(dem as real) / cast(total as real) * 100), " ") as sub_pct,
            count(geoid) as counts
        from results
        where lower(level) = "cty" and
            (
                (year = "2008" and lower(race) = "s") or
                (year = "2008" and lower(race) = "g")
            ) and
            cast(dem as real) > 0 and
            cast(total as real) > 0
        group by geoid
        order by geoid, year desc
    ) on geoid = sub_geoid and level = sub_level
    where results.year = "2008" and
        counts = 2 and
    	lower(race) = "s" and
    	lower(level) = "cty"
) on attributes.geoid = results_geoid;


-- speed-test2
attach database "tabular-data/comp_res.sqlite" as comp_res;
attach database "/usr/share/mapbox/project/compare-s-2008-cty-g-2008-cty/layers/elections.sqlite.index" as spatial_index;

SELECT count(*) FROM (select OGC_FID, GEOMETRY, (pct1 - pct2) as diff
from attributes
join (
    select geoid as results_geoid, (cast(dem as real) / cast(total as real) * 100) as pct1, 
    	(
    		replace(sub_pct, cast(dem as real) / cast(total as real) * 100, "")
    	) as pct2
    from comp_res.results
    join (
        select level as sub_level, geoid as sub_geoid, 
            group_concat((cast(dem as real) / cast(total as real) * 100), " ") as sub_pct,
            count(geoid) as counts
        from results
        where lower(level) = "cty" and
            (
                (year = "2008" and lower(race) = "s") or
                (year = "2008" and lower(race) = "g")
            ) and
            cast(dem as real) > 0 and
            cast(total as real) > 0
        group by geoid
        order by geoid, year desc
    ) on geoid = sub_geoid and level = sub_level
    where results.year = "2008" and
        counts = 2 and
    	lower(race) = "s" and
    	lower(level) = "cty"
) on attributes.geoid = results_geoid) WHERE OGC_FID IN (SELECT pkid FROM "idx_attributes_GEOMETRY" WHERE xmax>=-12836528.78209936 AND xmin<=-10958012.37496287 AND ymax>=4696291.017841229 AND ymin<=6574807.424977722);

~/projects/atlas-project[map]$ time ./speed-test2.sh
56

real 0m2.255s
user 0m2.190s
sys 0m0.063s
~/projects/atlas-project[map]$ time ./speed-test1.sh
377

real 0m1.026s
user 0m0.946s
sys 0m0.079s

speed-test1 explain:

1|0|0|SCAN TABLE results USING INDEX results_idx (~62500 rows)
1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|2|SCAN SUBQUERY 1 (~10 rows)
0|1|1|SEARCH TABLE results USING INDEX results_idx (geoid=?) (~2 rows)
0|2|0|SEARCH TABLE attributes USING AUTOMATIC COVERING INDEX (geoid=?) (~7 rows)

speed-test2 explain:

1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|SEARCH TABLE attributes USING INTEGER PRIMARY KEY (rowid=?) (~25 rows)
0|0|0|EXECUTE LIST SUBQUERY 2
2|0|0|SCAN TABLE idx_attributes_GEOMETRY VIRTUAL TABLE INDEX 2:DbBaDdBc (~0 rows)
0|1|2|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (counts=?) (~3 rows)
0|2|1|SEARCH TABLE results USING INDEX results_idx (geoid=?) (~2 rows)