On Thu, Jul 6, 2017 at 12:54 PM, Clemens Ladisch <clem...@ladisch.de> wrote:
> Dominique Devienne wrote:
> > with
> > kcounts(t, c) as (
> > select code_type, count(*)
> > from keys
> > group by code_type
> > ),
> > ...
> > select ...,
> > (select c from kcounts where t = 'SET') as "#sets",
> > (select c from kcounts where t = 'CST') as "#constants",
> > (select c from kcounts where t = 'CMT') as "#comments",
> > (select c from kcounts where t = 'LOG') as "#logs",
> > ...;
> >
> > the SQL as written does several passes over kcounts
>
> However, the subquery is flattened: each pass counts only rows with
> a specific code_type value.
>
> An index on code_type would help _very much_.
Actually not that much apparently.
No Simon, I didn't have an index on code_type.
In fact keys is itself a view, and cote_type a case expression.
That's fine, I created a expression index using
create index idx_keys_code on keys_tab(
case
when (code & 1) = 1 then 'SET'
when (code & 2) = 2 then 'CST'
when (code & 4) = 4 then 'CMT'
when (code & 8) = 8 then 'LOG'
else null
end
)
The plan correctly changes to "SCAN TABLE keys USING INDEX idx_keys_code",
4x.
And the runtime only drops to ~5.4s closer to 6.5s than 2.15s,
but at the cost of 22MB extra in the DB file for the index. Not sure that a
good tradeof.
> Is there a way to reformulate this query differently to somehow achieve a
> > single pass on the keys table? Or force the query planner to
> "instantiate"
> > the kcounts "CTE view" to achieve that single scan, via some kind of
> hint?
>
> It would be possible to prevent the subquery flattening
> (http://www.sqlite.org/optoverview.html#flattening), but AFAIK there is
> no hint that would force SQLite to recognize that all the instances of
> kcounts are then identical and can be reused.
>
> You could put kcounts into a temporary table.
>
I could it in a table, but then who updates kcounts when keys (or keys_tab)
changes?
I guess one can play with triggers then, to emulate materialized views,
opening a new can of worm.
These are "persistent report views", as already discussed when the thousand
separator was added to printf.
"Users" just "view" these views in any SQLite clients, typically a GUI one.
And they should remain "dynamic"
into case the table contents are modified with DMLs.
Maybe the "hint" to create an ephemeral table for a "CTE view" could be to
prefix the CTE view name with temp. ?
I know that Richard/SQLite are not much into planner hints, but a few
exists already.
What do you think Richard? --DD
> with
> temp.kcounts(t, c) as (
> select code_type, count(*)
> from keys
> group by code_type
> ),
> ...
> select ...,
> (select c from kcounts where t = 'SET') as "#sets",
> (select c from kcounts where t = 'CST') as "#constants",
> (select c from kcounts where t = 'CMT') as "#comments",
> (select c from kcounts where t = 'LOG') as "#logs",
> ...;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
