Re: [sqlite] ON CONFLICT with partial indexes
> However, suppose you had a partial index that keyed on > > "extra > 4" > > and your SELECT had the clause > > "WHERE extra > ?1" > > The parameter might be set to 7. You and I know that this means SQLite could use the partial index. But SQLite will not compare the 7 and the 4 and decide it can use the partial index. It does not know that 7 is greater than 4, because it doesn't look at the parameter's value before doing its optimization. Right -- I definitely understand the implications. My confusion is a result of not having realized that parameterized queries are somewhat of a second-class-citizen. That is, the same query will be treated differently depending on whether it uses literal values vs bind parameters. I'm not trying to debate or even question the rationale for these things -- I just am noting that I found this surprising, since using bind parameters is *always* what I do and suggest others to do. This is a bummer because in multiple apps I have created some partial indexes and did all my EXPLAIN analysis using the CLI and literal values, not realizing that once I started using the DB from my application these indexes would be unusable. On Mon, Feb 18, 2019 at 10:54 AM Simon Slavin wrote: > On 18 Feb 2019, at 4:41pm, Charles Leifer wrote: > > > Simon, I appreciate that, but your comment seems to contradict the > example I provided, as well as the example Dr. Hipp provided. Am I > misunderstanding? > > SQLite can compare two comparators. In Dr. Hipp's example the index says > > "extra IS NOT NULL" > > and the WHERE clause says > > "WHERE extra>?1" > > SQLite knows that for extra to be bigger than a number -- any number -- > extra cannot be NULL. So it can deduce that it can use that partial > index. That's what Dr. Hipp wrote. > > However, suppose you had a partial index that keyed on > > "extra > 4" > > and your SELECT had the clause > > "WHERE extra > ?1" > > The parameter might be set to 7. You and I know that this means SQLite > could use the partial index. But SQLite will not compare the 7 and the 4 > and decide it can use the partial index. It does not know that 7 is > greater than 4, because it doesn't look at the parameter's value before > doing its optimization. > > Simon. > ___ > 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
Re: [sqlite] ON CONFLICT with partial indexes
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 wrote:
>
>> On 2/17/19, Charles Leifer 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 c
Re: [sqlite] ON CONFLICT with partial indexes
On 18 Feb 2019, at 4:41pm, Charles Leifer wrote: > Simon, I appreciate that, but your comment seems to contradict the example I > provided, as well as the example Dr. Hipp provided. Am I misunderstanding? SQLite can compare two comparators. In Dr. Hipp's example the index says "extra IS NOT NULL" and the WHERE clause says "WHERE extra>?1" SQLite knows that for extra to be bigger than a number -- any number -- extra cannot be NULL. So it can deduce that it can use that partial index. That's what Dr. Hipp wrote. However, suppose you had a partial index that keyed on "extra > 4" and your SELECT had the clause "WHERE extra > ?1" The parameter might be set to 7. You and I know that this means SQLite could use the partial index. But SQLite will not compare the 7 and the 4 and decide it can use the partial index. It does not know that 7 is greater than 4, because it doesn't look at the parameter's value before doing its optimization. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ON CONFLICT with partial indexes
Simon, I appreciate that, but your comment seems to contradict the example I provided, as well as the example Dr. Hipp provided. Am I misunderstanding? On Mon, Feb 18, 2019, 9:44 AM Simon Slavin On 18 Feb 2019, at 3:23pm, Charles Leifer wrote: > > > 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)? > > SQLite knows before then. The SQLite query optimizer does not do > algebra. It does its job without knowing what values you will plug into > the parameters. > > Indexes are chosen by looking at how you are comparing certain columns: > whether you compare a column using "<" or ">" or "=" or some other > comparator. The values you're comparing them with are ignored. > > Simon. > ___ > 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
Re: [sqlite] ON CONFLICT with partial indexes
On 18 Feb 2019, at 3:23pm, Charles Leifer wrote: > 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)? SQLite knows before then. The SQLite query optimizer does not do algebra. It does its job without knowing what values you will plug into the parameters. Indexes are chosen by looking at how you are comparing certain columns: whether you compare a column using "<" or ">" or "=" or some other comparator. The values you're comparing them with are ignored. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ON CONFLICT with partial indexes
On Mon, Feb 18, 2019 at 4:38 PM Richard Damon wrote: > Remember the query plan is determined when that statement is compiled, > which is BEFORE you do the binding of the parameters, so the plan can not > depend on the value of parameters. There is no later attempt to optimize > once the values are known, as in general, this isn’t apt to help (you have > one case where it could, but to help that case, you would need to hurt a > lot of other more common cases). > Actually, "bind peeking" to find a more optimal plan does often help. Otherwise "non-lite" RDBMS wouldn't do it. The fact SQLite does not implement, does not mean it's not a useful thing to do. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ON CONFLICT with partial indexes
> On Feb 18, 2019, at 10:23 AM, Charles Leifer wrote: > > 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? > Remember the query plan is determined when that statement is compiled, which is BEFORE you do the binding of the parameters, so the plan can not depend on the value of parameters. There is no later attempt to optimize once the values are known, as in general, this isn’t apt to help (you have one case where it could, but to help that case, you would need to hurt a lot of other more common cases). ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 wrote:
> On 2/17/19, Charles Leifer 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
Re: [sqlite] ON CONFLICT with partial indexes
On 2/17/19, Charles Leifer 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
Re: [sqlite] ON CONFLICT with partial indexes
Olivier, what do you mean "snapshot"? What is a release? How can I find out about the SQLite "releases" you are talking about? On Sun, Feb 17, 2019 at 3:23 PM Olivier Mascia wrote: > > Le 17 févr. 2019 à 22:05, Charles Leifer a écrit : > > > > I run against the latest and greatest. > > > > Python: > > > > In [1]: import sqlite3 > > > > In [2]: sqlite3.sqlite_version > > Out[2]: '3.28.0' > > > > > > Sqlite: > > > > sqlite> select sqlite_version(), sqlite_source_id(); > > 3.28.0|2019-02-12 22:58:32 > > 167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0 > > The latest release is 3.27.1 ( > https://www.sqlite.org/releaselog/3_27_1.html). > > The code you use comes from a snapshot of the code ( > https://sqlite.org/src/info/167b91df77fff1a8) right in the middle of work > in progress after 3.27.1 was released and (probably) long before 3.28 is. > > That may not be related to the issue you raised, but you'd probably be > well advised to make sure you run the latest *released* code, because the > releases go through a huge test/validation procedure. > > -- > Best Regards, Meilleures salutations, Met vriendelijke groeten, > Olivier Mascia > > > ___ > 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
Re: [sqlite] ON CONFLICT with partial indexes
> Le 17 févr. 2019 à 22:05, Charles Leifer a écrit : > > I run against the latest and greatest. > > Python: > > In [1]: import sqlite3 > > In [2]: sqlite3.sqlite_version > Out[2]: '3.28.0' > > > Sqlite: > > sqlite> select sqlite_version(), sqlite_source_id(); > 3.28.0|2019-02-12 22:58:32 > 167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0 The latest release is 3.27.1 (https://www.sqlite.org/releaselog/3_27_1.html). The code you use comes from a snapshot of the code (https://sqlite.org/src/info/167b91df77fff1a8) right in the middle of work in progress after 3.27.1 was released and (probably) long before 3.28 is. That may not be related to the issue you raised, but you'd probably be well advised to make sure you run the latest *released* code, because the releases go through a huge test/validation procedure. -- Best Regards, Meilleures salutations, Met vriendelijke groeten, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ON CONFLICT with partial indexes
I run against the latest and greatest. Python: In [1]: import sqlite3 In [2]: sqlite3.sqlite_version Out[2]: '3.28.0' Sqlite: sqlite> select sqlite_version(), sqlite_source_id(); 3.28.0|2019-02-12 22:58:32 167b91df77fff1a84791f6ab5f72239b90475475be690a838248119b6dd312f0 On Sun, Feb 17, 2019 at 2:58 PM Simon Slavin wrote: > On 17 Feb 2019, at 8:51pm, Charles Leifer wrote: > > > Is this a bug? > > Just to make things easier, which version of SQLite are you using in your > Python SQLite library ? If you don't know, you can find this out using > > SELECT sqlite_version(); > SELECT sqlite_source_id(); > > The second may give a blank result or an error, which is fine. > > Simon. > ___ > 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
Re: [sqlite] ON CONFLICT with partial indexes
On 17 Feb 2019, at 8:51pm, Charles Leifer wrote: > Is this a bug? Just to make things easier, which version of SQLite are you using in your Python SQLite library ? If you don't know, you can find this out using SELECT sqlite_version(); SELECT sqlite_source_id(); The second may give a blank result or an error, which is fine. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
