On 11/3/16, Richard Hipp <[email protected]> wrote: > On 11/3/16, Jens Alfke <[email protected]> wrote: >> Does the query optimizer coalesce identical calls to deterministic >> functions? I’m thinking in particular of a statement like: >> >> CREATE INDEX byFoo ON jtable (json_extract(body, ‘$.foo’)) WHERE >> json_extract(body, ‘$.foo’) IS NOT NULL >> >> where it would improve performance if the JSON were not parsed twice. > > In the case of the CREATE INDEX statement, no. On the other hand, > json_extract() is able to parse JSON at a rate of over 300MB/s so it > might not be as sluggish as you expect. > > If you subsequently do something like: > > SELECT * FROM jtable WHERE json_extract(body, '$.foo')=25; > > Then the json_extract() function is not called at all. Not even once. > So under the theory that queries are more common than CREATE INDEX > statements, your total number of calls to json_extract seem like they > will be minimal.
One other thing: You can use the EXPLAIN feature to figure out the above for yourself, for your specific schema and query. If you find some combination of indexes and queries for which you think function coalescing might be helpful, then by all means come back and present your argument here. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
