Re: [sqlite] Doing math in sqlite

[Shevek]

For the cost of a single table scan, you may be better with:

select max(foo) - min(foo) where etc.

S.

--
Anyone using floating point for financial computations needs their head 
examined.


On 12/20/18 4:32 PM, Jens Alfke wrote:




On Dec 20, 2018, at 4:21 PM, Jungle Boogie  wrote:

select od_reading from mileage where car='foo' limit 1
select od_reading from mileage where car='bar' order by od_reading desc limit 1


Note: the first query should use “order by od_reading”, otherwise the order is 
undefined.

A clearer way to specify these is
select min(od_reading) from mileage where car=‘foo’
select max(od_reading) from mileage where car=‘foo’


How do I subtract the last query from the first query to determine how many
miles were driven?


It’s super simple:

(select od_reading from mileage where car='foo' limit 1) - (select od_reading 
from mileage where car='bar' order by od_reading desc limit 1)

—Jens
___
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] Either bad constant or bad comment in os_unix.c

[Shevek]

In the nolock IOMETHODS:

https://github.com/mackyle/sqlite/blob/7bd4fc81a71bdc777151c747b2e6d3ee58994251/src/os_unix.c#L5203

Either the constant should be '1' or the comment should permit mmap. I 
suspect the constant should be 1?


S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] mmap, madvise, mlock and performance

[Shevek]

On 08/03/2018 12:55 PM, Simon Slavin wrote:

On 3 Aug 2018, at 8:36pm, Shevek  wrote:


We are running a 100Gb sqlite database, which we mmap entirely into RAM. We are 
having trouble with parts of the disk file being evicted from RAM during 
periods of low activity causing slow responses, particularly before 9am. Has 
anybody played with mlock and/or madvise within the sqlite mmap subsystem to 
improve this behaviour?


Is this a genuine Linux machine running on physical hardware, or is it a 
virtual machine ?


Yes, it's a genuine physical, we have Xeon and Epyc CPUs available. 
Sometimes we have to run in VMs (up to 50Gb), but the bigger stuff is 
all physical. We typically have 256Gb+ of RAM, so we aren't under 
particular pressure to mmap a 100Gb database.



Are you intentionally doing anything that would contend for this memory ?  In 
other words, when a memory-mapped portion gets swapped out, does it make sense 
what replaced it, or is it pointless and weird ?


Sometimes, Linux just seems to get unfriendly with a set of pages and 
just maps them out. I've been watching it all weekend - right now the 
system I'm watching has 165Gb free, and I watched Linux just dump 40Gb 
out of RAM. :-( There are other jobs running on the system, and doing 
I/O, but nothing that should put any real memory pressure on the system, 
aside from disk I/O, backup, etc.


We're about to try mlockall(MCL_FUTURE) along with MAP_SHARED. It might 
also be worth trying fadvise(), but I think kernel only honours a few 
megabytes based on that. We did think of a page-toucher thread but that 
risks thrashing as much as anything, but might be interesting for 
monitoring page faults performance.


Later note: mlockall() failed because of JVM heap; we're going to have 
to do something much more specific, like holding a secondary map and 
mlocking that.


Warren:

Is the copy-everything-into-memory strategy not prohibitively expensive 
at the 100+Gb scale? Is it worth sinking the time into implementing 
that? Our rows are very small, only a few bytes each, so the per-row 
overhead may be significant. Also, it would be nice to have a shared 
mmap, rather than entirely private RAM, so we can run experiments over 
the shared (readonly) store.


S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] mmap, madvise, mlock and performance

[Shevek]

Hi,

We are running a 100Gb sqlite database, which we mmap entirely into RAM. 
 We are having trouble with parts of the disk file being evicted from 
RAM during periods of low activity causing slow responses, particularly 
before 9am. Has anybody played with mlock and/or madvise within the 
sqlite mmap subsystem to improve this behaviour?


The system has a few hundred gig of RAM, no swap, the database is 
read-only, and we would prefer a page-out to a process crash, so mlock 
might not be ideal, but madvise might not be strong enough?


Thank you.

S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] random rows

[Shevek]

You may find it faster to do:

select c from t where rowid in (list-of-constants)

and generate the list of constants using something like a blackrock 
permutation generator. That takes linear time, whereas all the order-by 
variants are n.log(n). You need some sort of row-id generator function, 
which you can do with windows/olap. Beware in Oracle, some of the rowid 
variant functions only increment if you return the row.


sqlite seems to have a trick where copying the table creates sequential 
rowids, by spec, if you don't have one.


S.

On 05/31/2018 08:28 PM, Jay Kreibich wrote:


I’m not entirely sure your solution will have an even distribution.  It depends 
a lot on how many times random() is called (once per row vs once per sort 
operation), and how the sort algorithm works.  I might do this instead:

SELECT * FROM (SELECT random(), t.* FROM t) ORDER BY 1 LIMIT 200;

As this makes sure random() is only called once per row.  I’m pretty sure this 
is actually equivalent to yours, but it makes things a bit more explicit.



If you only needed one, I’d do something like:

SELECT * FROM table ORDER BY rowid LIMIT 1 OFFSET abs(random()) % (SELECT 
count(*) FROM table);

Or even just call that multiple times if you need a moderate number.  The ORDER 
BY should be free, but it could also be eliminated.

   -j




On May 31, 2018, at 7:12 PM, Torsten Curdt  wrote:

I need to get some random rows from a large(ish) table.

The following seems to be the most straight forward - but not the fastest.

  SELECT * FROM table ORDER BY random() limit 200

Is there a faster/better approach?

cheers,
Torsten
___
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

Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

[Shevek]

SQL overall is phenomenally ambiguous about group-by:

1) select a0 + 1 as a0 from a group by a0

has a different behaviour in different SQL implementations, depending on 
whether the group-by prefers the underlying column or the projection alias.


2) As you rightly point out, group by integer has a different behaviour 
in different implementations, depending on how they range-check for "is 
a projection reference" - is a valid integer, is out of range, ...


3) group by expression has several of the above ambiguities, when it's 
legal.


4) Everything else is AFU'd too.

S.

On 05/31/2018 09:49 AM, Mark Brand wrote:
At first I  wasn't sure what you meant by "column identifier". It's what 
the documentation calls "column-alias". But this also qualifies as an 
"other expression" doesn't it? A column alias can appear pretty much 
anywhere any expression can, except in the same column list where it is 
defined.


Mark


On 31/05/18 18:27, Hick Gunter wrote:

Ponder the following select fragment

SELECT name, year * 100 + month as period, day + hour / 24.0 from 
some_table


Then you can have

ORDER BY 1,2,3 -- integer output column numbers

which is equivalent to

ORDER BY name, period, day + hour / 24.0 -- output column identifier 
x2 + "any other expression"


-Original Message-
From: sqlite-users 
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of 
Mark Brand

Sent: Mittwoch, 30. Mai 2018 16:22
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks for the clarification.


You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY.
Just to make sure I'm not missing something subtle: I understand the 
"constant integer" is what gets interpreted as a result column number. 
What is an "output column identifier" then? Isn't it already covered 
by the broader category "any other expression"?


It's still a pretty astonishing language feature(!?) that an integer 
numeric-literal, which in every other column-like context represents 
its integer value, gets interpreted after GROUP BY or ORDER BY as a 
result column number. Fortunately, SQLite isn't to blame for designing 
this.


By the way, this feature is documented for ORDER BY, but I don't see 
it for GROUP BY.


Mark

On 30/05/18 13:28, Hick Gunter wrote:
You have constant integers, output column identifiers and "any other 
expression" as terms for GROUP BY. If the expression evalutes to a 
constant value, you will have only one output row.


-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 12:11
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] unexpected error with "GROUP BY 0"

Thanks. I had forgotten that GROUP BY considers a literal integer in 
this context to be a column number, a feature I don't use.


These, on the other hand, work as I would have expected:

sqlite> select 0 group by cast (0 as int);
0
sqlite> select 0 group by (select 0);
0

Mark


On 30/05/18 12:00, Hick Gunter wrote:
Yes. If the expression is a constant integer K, then it is 
considered an alias for the K-th column of the result set. Columns 
are ordered from left to right starting with 1.


There is no 0-th column, so GROUP BY 0 is "out of range", just the 
same as "SELECT 0 GROUP BY 31" would be.


-Ursprüngliche Nachricht-
Von: sqlite-users
[mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von
Mark Brand
Gesendet: Mittwoch, 30. Mai 2018 11:32
An: SQLite mailing list 
Betreff: [EXTERNAL] [sqlite] unexpected error with "GROUP BY 0"

Hi,

Is there a good reason for this error:

sqlite> SELECT  0 GROUP BY 0;
Error: 1st GROUP BY term out of range - should be between 1 and 1
sqlite> SELECT 0 GROUP BY 1;
0

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
    Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the 
addressee.

___
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


___
   Gunter Hick | Software Engineer | Scientific Games International
GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR:
0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not 

Re: [sqlite] [EXTERNAL] Window functions

[Shevek]

About storing the whole result set: Note that in postgresql-derivatives 
(and Oracle? maybe Teradata?), this is valid:


select lag(a0, a0) over () from a;

whereas many other servers (which I won't name) require the second 
argument of lag() to be constant. If it is constant (even in 
postgresql-derivatives), a better window bound than the implicit 
"unbounded preceding" can be computed by the optimizer, and this may be 
essential for an efficient implementation of the most common case, 
because then the window buffer (per-group) can be guaranteed to fit into 
(pre-allocated) RAM.


S.

On 04/26/2018 07:44 AM, Hick Gunter wrote:

"Window functions" aka "scrollable cursors" require that the whole result set 
ist stored somewhere. Or at least the current position of all of the tables/indices involved in 
producing it, provided that such positions are settable. You then need to keep track of how many 
records need to be produced to satisfy the window position.

If you really need scrollable cursors, you are free to implement them, maybe by

CREATE TEMP TABLE query_results AS SELECT...

Followed by

SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end;

And cleaning up with

DROP TABLE query_results;

Note that you will be producing the complete result set before you are able to 
return even only the first row. This defeats any gain from having the query 
return results in the desired order without requiring a sort, i.e. choosing the 
indices that makes the desired order coincide with the natural order.



-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Charles Leifer
Gesendet: Mittwoch, 25. April 2018 21:05
An: General Discussion of SQLite Database 
Betreff: [EXTERNAL] [sqlite] Window functions

Hi,

I'm sure this has been asked before, but are window functions on the roadmap? 
Is it the authors' experience that the implementation would significantly 
complicate sqlite? Just curious. Thanks so much for a fantastic library.

Charlie
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] UPSERT available in pre-release

[Shevek]

Opinion: Of all the DBMS's UPSERT/MERGE semantics, postgresql's is the 
least useful because it's very limited: It can only do a check against a 
constraint, and the cost of evaluating that constraint has to be carried 
by all other statements which mutate the table. Oracle/Teradata MERGE is 
a far more useful semantics because it's defined more like a self-join, 
where the constraint is specified in the statement, not the DBMS.


On 04/19/2018 11:29 AM, Richard Hipp wrote:

The latest pre-release snapshot [1] contains support for UPSERT
following the PostgreSQL syntax.  The documentation is still pending.
Nevertheless, early feedback is welcomed.  You can respond either to
this mailing list, or directly to me.


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Are you getting spam when you post to sqlite-users ?

[Shevek]

It has to be a subscriber. I just got spam in the form of an 
almost-immediate reply to my last message to the list, including 
message-ids. Web wouldn't bother including that metadata, and is 
unlikely to be quite so real-time.


On 04/18/2018 11:35 PM, Simon Slavin wrote:

On 18 Apr 2018, at 11:19pm, dave  wrote:


Wouldn't it be as simple as subscribing to the mailing list and harvesting
the emails directly from the inbound content?


No need to subscribe.  This mailing list is gated to web fora.  Just scrape 
some web pages.

I don't think there's a solution short of each one of us tuning our junkmail 
filters by telling them to reject all of those spam messages.

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] JDBC driver experience

[Shevek]

1. Xerial
2. A year or so, relatively heavily.

3.
 * MAX_MMAP_SIZE is too small by a few hundred megabytes. It's safe to 
set to around a terabyte.
 * No way to effectively use multiple threads, even on a read-only 
mmap'd database.
 * Planner sometimes misses plans - does it need better STAT features 
enabled?


4. What immediately comes to mind:

For writing:
 * JournalMode MEMORY
 * SynchronousMode OFF
 * A couple of hundred meg of page cache
 * Use quite big transactions, but it doesn't really matter where the 
boundaries are. You can do 50K+ writes per transaction.

 * Use Java-side locks.
 * Any data which doesn't need to be accessible to the relational 
engine should be serialized into a BLOB with Kryo and Snappy. Even 
simple text fields.



For reading:
 * mmap EVERYTHING
 * JournalMode OFF
 * It's MUCH faster to join in Java than in sqlite. If you're loading 
data into memory, do an application-side hash-join.

 * Wrap all your queries in a performance rig and log slow queries.
 * Force the planner using the CROSS keyword for any query which is 
ever slow.
 * Sometimes the planner changes the query plan based on e.g. the size 
of an IN-list.
 * Use covering indexes, and use sqlite_analyzer to figure out which 
ones are winning a space-time trade-off.
 * The partial covering index optimizer doesn't always account for 
constants, and we lose a bunch of space because we have to include the 
constant values in the index.



Overall:
 * Does anyone respond to issues?


We actually use nondeterministic transaction boundaries for writing 
because it really doesn't matter where they are, it only matters that 
you have one.


S.

On 04/18/2018 08:34 PM, Richard Hipp wrote:

Are you using SQLite with JDBC?  If so, can you please answer a few
questions below?

You can post on this mailing list or send your reply directly to me.

1. Which JDBC are you using?
2. For how long have you been using it?
3. What issues you had with this driver?
4. What advise do you have for avoiding problems in this driver?


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] High performance and concurrency

[Shevek]

On 03/01/2018 01:24 AM, Hick Gunter wrote:

Use 1 connection for each thread. Sharing a connections between threads may 
cause transactions to be larger than each thread thinks.


Why would I have a transaction of non-zero size on a read-only connection?

It looks from the source as if having bCoreMutex=true and 
bFullMutex=false will allow us the concurrency we need. I'm going to try 
again in a couple of days.


Our journal_mode is OFF.

We use HikariCP, so a connection is in use by one thread at a time with 
JMM-safe handoff, and they all share the mmap region.


S.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Shevek
Gesendet: Donnerstag, 01. März 2018 09:10
An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>; Simon Slavin 
<slav...@bigfraud.org>
Betreff: [EXTERNAL] Re: [sqlite] High performance and concurrency

On 02/28/2018 11:45 PM, Simon Slavin wrote:


On 1 Mar 2018, at 7:24am, Shevek <sql...@anarres.org> wrote:


What I think is happening is that either a pthread mutex or a database lock is 
serializing the accesses, so each thread blocks the others.


To be specific, I'm concerned about is the line
sqlite3_mutex_enter(db->mutex) at the top of sqlite3_step(). Since my queries are 
spending all their time in sqliteVdbeExec(), which is reached through that path, I 
assume db->mutex is preventing concurrency.

Our main hotspots in the query are sqliteVdbeExec() and updating the btree 
pointer to point to a new page (I forget the call name). We can't do much about 
the cost of execution; we've mmap'd everything to avoid the I/O, we're using 
covering indexes to help with locality, we've sorted our query keys to attempt 
to reduce index page seeks, and now we want to use concurrency and splitting 
the logic in our query to exploit memory bandwidth.

Now I've traced this again, I'm looking warily at SQLITE_OPEN_NOMUTEX because 
we need thread-safety, as in, sqlite's internal data structures must be handled 
correctly in the presence of multiple threads or passing a connection between 
threads (safely in the JMM); we just don't need serialization of database reads 
and writes, because nothing we do has a serializable side-effect. Is 
SQLITE_OPEN_NOMUTEX the answer?


What journal mode are you using ?


I'm fairly sure journal mode is NONE for our readonly database. Anyway, 
readonly shouldn't write to a journal. We have confirmed that the md5sum of the 
database file is unchanged during and after the execution of our application.

S.


<https://sqlite.org/pragma.html#pragma_journal_mode>

If it's not WAL, try WAL.  If it's currently WAL, try DELETE.  Once you've 
changed it see if this changes how your program behaves.

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


___
  Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
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] High performance and concurrency

[Shevek]

On 02/28/2018 11:45 PM, Simon Slavin wrote:


On 1 Mar 2018, at 7:24am, Shevek <sql...@anarres.org> wrote:


What I think is happening is that either a pthread mutex or a database lock is 
serializing the accesses, so each thread blocks the others.


To be specific, I'm concerned about is the line 
sqlite3_mutex_enter(db->mutex) at the top of sqlite3_step(). Since my 
queries are spending all their time in sqliteVdbeExec(), which is 
reached through that path, I assume db->mutex is preventing concurrency.


Our main hotspots in the query are sqliteVdbeExec() and updating the 
btree pointer to point to a new page (I forget the call name). We can't 
do much about the cost of execution; we've mmap'd everything to avoid 
the I/O, we're using covering indexes to help with locality, we've 
sorted our query keys to attempt to reduce index page seeks, and now we 
want to use concurrency and splitting the logic in our query to exploit 
memory bandwidth.


Now I've traced this again, I'm looking warily at SQLITE_OPEN_NOMUTEX 
because we need thread-safety, as in, sqlite's internal data structures 
must be handled correctly in the presence of multiple threads or passing 
a connection between threads (safely in the JMM); we just don't need 
serialization of database reads and writes, because nothing we do has a 
serializable side-effect. Is SQLITE_OPEN_NOMUTEX the answer?



What journal mode are you using ?


I'm fairly sure journal mode is NONE for our readonly database. Anyway, 
readonly shouldn't write to a journal. We have confirmed that the md5sum 
of the database file is unchanged during and after the execution of our 
application.


S.


<https://sqlite.org/pragma.html#pragma_journal_mode>

If it's not WAL, try WAL.  If it's currently WAL, try DELETE.  Once you've 
changed it see if this changes how your program behaves.

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

[sqlite] High performance and concurrency

[Shevek]

Hi,

I would like to have truly concurrent access to an sqlite database, that 
is, the ability for multiple connections to read from the database 
simultaneously. I'm using Java with xerial's sqlite-jdbc, customized to 
let me mmap the entire database into RAM, and with additional debugging 
symbols for perf. The database is about 30Gb, fully read-only, and the 
connections are opened as such.


What I think is happening is that either a pthread mutex or a database 
lock is serializing the accesses, so each thread blocks the others.


Queries are taking a few seconds, even with covering indexes, and I have 
the RAM bandwidth available, so I'd really like to use it.


Any pointers?

Thank you.

S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missing several important tricks in partial indexes

[Shevek]

If I create a partial index:

create table a (a0, a1)
create index idx on a (a0) where a1 is null;

Then we have several issues:

1) This should be a covering index for

select a0, a1 from a where a1 is null;

It isn't. It's a great index, but we still defer to the table to look up 
the (always null) values of a1.


2) Actually, it's slightly worse than that:

select a0, null as a1 from a where a1 is null;

will select the index, because the condition a1 matches, but it seems 
that it's STILL not considered a covering index, (guessing now) 
presumably because it's not SUFFICIENT that the condition on the query 
is a superset of the query on the index.


Including a1 in the index makes it a covering index, despite the fact 
that a1 is always null.


I care more about this than most algebraic manipulations because it's a 
whole extra order of I/O in a lookup, and it's turning a 1-second query 
into a 51-second query, which is now ineligible for use in a web page. 
Right now, it's more worthwhile to pay the space penalty for the 
additional constant value at the end of the index.




3) The third issue is almost more major than the preceding two, and has 
nothing to do with covering, but with partial index selection AT ALL:


If we do a select "WHERE a1 IS ?" and pass a null for the bind-value of 
? it will never select the partial index, even though it's eligible.


S.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users