The only thing which MIGHT (note that I said MIGHT, you will have to try and see whether it does or not) make a difference is if you have STAT4 enabled and have generated statistics on your database. When STAT4 has been enabled AND you have statistics THEN the query plan is re-prepared after a BIND which changes information that may be relevant to the selection of INDEXES used by the planner. This obviously means that the WHERE clause is now fully specified (because the plan is regenerated after the bind) and the planner MAYHAPS SO or MAYHAPS NOT make a different choice.
The difference is that with STAT4 enabled, every query that has bound parameters that could affect plan generation will be reprepared on the first step at execution time taking the value of the bindings into account. If the time taken to "double prepare" the plan on every statement execution is greater than the time taken to execute the statement using the generic (first prepared plan), then the overall result will (wall clock elapsed time from GO to DONE) will be greater than if you did not use STAT4. You will only know whether that is effective in your case is by giving it the old college try. You may find that the use of the regenerated "particular" plan is significantly more performant to be worthwhile ... or you may find that the overhead of the re-prepare reduces performance over just using the generic plan. Of course, I do not know if having the appropriate STAT4 statistics and the re-prepare after the BIND will result in using your partial index in this particular instance without going and testing it. Richard may know off-hand, but I do not. --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Charles Leifer >Sent: Monday, 18 February, 2019 08:23 >To: SQLite mailing list >Subject: Re: [sqlite] ON CONFLICT with partial indexes > >Thanks for the explanation. So does this mean that two identical >queries >can have completely different query plans depending on whether they >use a >parameters vs literal values embedded in the SQL string? > >> But in this case with the WHERE clause is incompletely specified, >there >is no way for the query planner to do that. > >At some point before actually executing the query I'm calling >sqlite3_bind...() to set the value of the parameter placeholder. >Presumably >SQLite would know at that point that a certain index is usable (or in >this >case, that a certain constraint is relevant)? > >This seems to me to have the effect that partial indexes are in many >ways >ineffective when mixed with parameterized queries. I understand that >they >might be applicable in cases where very general assumptions about >null/not-null can be inferred, but that's not what I would expect as >an >end-user. > >On Sun, Feb 17, 2019 at 5:26 PM Richard Hipp <d...@sqlite.org> wrote: > >> On 2/17/19, Charles Leifer <colei...@gmail.com> wrote: >> > I'm having trouble executing an INSERT ... ON CONFLICT with a >partial >> > index. It works fine in the SQLite shell, but it fails when I >express the >> > conflict constraint using a parameterized query. >> > >> > For example: >> > >> > CREATE TABLE ukvp ("id" integer primary key, "key" text not null, >"value" >> > int not null, "extra" int not null); >> > CREATE UNIQUE INDEX ukvp_partial ON ukvp (key, value) WHERE extra >> 1; >> > >> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, >?), (?, >> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > ?) DO UPDATE >SET >> > "extra" = EXCLUDED."extra"'' >> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3, 1)) >> > >> > But the following works: >> > >> > sql = 'INSERT INTO "ukvp" ("key", "value", "extra") VALUES (?, ?, >?), (?, >> > ?, ?) ON CONFLICT ("key", "value") WHERE ("extra" > 1) DO UPDATE >SET >> > "extra" = EXCLUDED."extra"'' >> > db.execute(sql, ('k1', 1, 2, 'k2', 2, 3)) >> > >> > Is this a bug? >> >> No, this is not a bug. >> >> The purpose of the WHERE clause in an upsert is to match the >> constraint against a particular UNIQUE index. The query planner >must >> know which index constraint is failing in order to generate correct >> code for the upsert. But in this case with the WHERE clause is >> incompletely specified, there is no way for the query planner to do >> that. >> >> For the same reason, this query: >> >> SELECT value FROM ukvp WHERE key=?1 AND extra>?2; >> >> will *not* use the partial index, but this query: >> >> SELECT value FROM ukvp WHERE key=?1 AND extra>1; >> >> *will* use the partial index. (Run each of the above with EXPLAIN >> QUERY PLAN to see for yourself.) >> >> The query planner will never use a partial index unless it can >prove >> at SQL-statement compile-time that the WHERE clause of the query >> implies that the WHERE clause of the partial index. With your >partial >> index, you can never prove anything about the truth of the >condition >> if the query contains "extra>?1". However, if your partial index >had >> said "WHERE extra IS NOT NULL", then the partial index would be >usable >> in all of the above situations, since "extra>?1" does indeed imply >> that "extra IS NOT NULL". >> -- >> D. Richard Hipp >> d...@sqlite.org >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite- >users >> >_______________________________________________ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
