Re: [sqlite] Hints for the query planner

2013-10-02 Thread Nico Williams
On Wednesday, October 2, 2013, Richard Hipp wrote: > > In the current beta (at http://www.sqlite.org/download.html) the function > is either unlikely(X) or likelihood(X,Y). In the second form, Y must be a > floating point constant between 0.0 and 1.0, inclusive. The first form is > equivalent to

Re: [sqlite] Hints for the query planner

2013-10-02 Thread Ryan Johnson
On 02/10/2013 2:19 AM, Baruch Burstein wrote: On Fri, Sep 13, 2013 at 5:29 AM, Ryan Johnson wrote: -- Join cardinality: Bach was a *very* prolific composer whose output likely dwarfs the (surviving) output of his contemporaries select p.title, c.name, p.year from

Re: [sqlite] Hints for the query planner

2013-10-02 Thread Richard Hipp
On Wed, Oct 2, 2013 at 1:40 AM, Nico Williams wrote: > On Tuesday, September 10, 2013, Richard Hipp wrote: > > > SURVEY QUESTION: > > > > The question for today is what to call this magic hint function: > > > > (1) unlikely(EXPR) > > (2) selective(EXPR) > > (3)

Re: [sqlite] Hints for the query planner

2013-10-02 Thread Baruch Burstein
On Fri, Sep 13, 2013 at 5:29 AM, Ryan Johnson wrote: > -- Join cardinality: Bach was a *very* prolific composer whose output > likely dwarfs the (surviving) output of his contemporaries > select p.title, c.name, p.year from composers c join pieces p on p.c_id = > c.id

Re: [sqlite] Hints for the query planner

2013-10-01 Thread Nico Williams
On Tuesday, September 10, 2013, Richard Hipp wrote: > SURVEY QUESTION: > > The question for today is what to call this magic hint function: > > (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) (1), on account of: it's already

Re: [sqlite] Hints for the query planner

2013-09-30 Thread John G
Dr Hipp I second Kyan's suggestion of a pseudo-comment hinting syntax: ... that the planner hint is not interleaved inside normal SQL > syntax. Instead I propose a special comment-like syntax instead, as > Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">": > Having had to

Re: [sqlite] Hints for the query planner

2013-09-12 Thread ibrahim
On 10.09.2013 21:26, Richard Hipp wrote: SURVEY QUESTION: The question for today is what to call this magic hint function: (1) unlikely(EXPR) (2) selective(EXPR) (3) seldom(EXPR) (4) seldom_true(EXPR) (5) usually_not_true(EXPR) A function call like proposed is much better than

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Ryan Johnson
On 12/09/2013 7:12 PM, Simon Slavin wrote: On 12 Sep 2013, at 11:35pm, Roger Binns wrote: On 12/09/13 05:03, Gabor Grothendieck wrote: Perhaps indexing the expression in question would be an alternative that would keep the performance info separate from the select.

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Simon Slavin
On 12 Sep 2013, at 11:35pm, Roger Binns wrote: > On 12/09/13 05:03, Gabor Grothendieck wrote: >> Perhaps indexing the expression in question would be an alternative >> that would keep the performance info separate from the select. > > I'd rather just do 'ANALYZE query'

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Roger Binns
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/09/13 05:03, Gabor Grothendieck wrote: > Perhaps indexing the expression in question would be an alternative > that would keep the performance info separate from the select. I'd rather just do 'ANALYZE query' and have SQLite go off and do

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Gabor Grothendieck
PostgreSQL supports create index on ( ) Note that it allows an expression and not just a column name. See: http://www.postgresql.org/docs/9.1/static/sql-createindex.html Perhaps indexing the expression in question would be an alternative that would keep the performance info separate

Re: [sqlite] Hints for the query planner

2013-09-12 Thread Klaas V
Richard  wrote: The question for today is what to call this magic hint function: (1)  unlikely(EXPR) (2)  selective(EXPR) (3)  seldom(EXPR) (4)  seldom_true(EXPR) (5)  usually_not_true(EXPR) Please feel free to suggest other names if you think of any.   I dislike #4 and#5, but what about one

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Daniel Polski
probability(EXPR, value) - Would force the user to set their best guess. I would think that the users guess would be more accurate than a general guess? What's considered unlikely for the users data, 0.05? 0.20? I would prefer if the mechanism could be handled with pragmas (or something)

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Doug Currie
On Sep 10, 2013, at 6:23 PM, Scott Robison wrote: > I think I prefer something along the lines of "unlikely" or "likely". The > problem with a term like "selective" (at least in my brain) is that it > doesn't imply (for the single argument version) in what way it is

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Ned Fleming
On Tue, 10 Sep 2013 15:26:51 -0400, Richard Hipp wrote: >SURVEY QUESTION: > >The question for today is what to call this magic hint function: > >(1) unlikely(EXPR) >(2) selective(EXPR) >(3) seldom(EXPR) >(4) seldom_true(EXPR) >(5) usually_not_true(EXPR) > >Please feel free

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Stephan Beal
On Wed, Sep 11, 2013 at 5:36 PM, Kevin Benson wrote: > On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp wrote: > > SURVEY QUESTION: > > (1) unlikely(EXPR) > > (2) selective(EXPR) > > (3) seldom(EXPR) > > (4) seldom_true(EXPR) > > (5)

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Kevin Benson
On Tue, Sep 10, 2013 at 3:26 PM, Richard Hipp wrote: > SURVEY QUESTION: > > The question for today is what to call this magic hint function: > > (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) (6)

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Ralf Junker
On 11.09.2013 16:07, Ryan Johnson wrote: Perhaps you meant "demote" rather than "degrade" ? That would be a better fit (an external action that does not necessarily make the object worse or less useful), and less vague, but it still carries a negative connotation. "demote" sounds fine to me,

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Ralf Junker
I suggest a verb to express what the function is actually doing, namely to reduce its argument in rank or degree for the query planner: DEGRADE 1. to reduce in worth, character, etc; disgrace; 2. to reduce in rank, status, or degree; remove from office; 3. to reduce in strength, quality,

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Konrad Hambrick
> -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf > Of Richard Hipp > Sent: Tuesday, September 10, 2013 2:27 PM > To: General Discussion of SQLite Database > Subject: [sqlite] Hints for the query planner > > There is a

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Baruch Burstein
I also think it should not be directly in the SQL. I like the not-really-a-comment syntax. Another option might be a few PRAGMAs, something like PRAGMA hint("table1.col1 IN (1,2,5)", 0.05); PRAGMA hint("table1.col2 LIKE '%bach%'". 0.4); these would add the hints to an internal table. When

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Hick Gunter
(6) maybe(EXPR) -Ursprüngliche Nachricht- Von: Richard Hipp [mailto:d...@sqlite.org] Gesendet: Dienstag, 10. September 2013 21:27 An: General Discussion of SQLite Database Betreff: [sqlite] Hints for the query planner There is a survey question at the bottom of this message. But first

Re: [sqlite] Hints for the query planner

2013-09-11 Thread Tony Papadimitriou
How about: maybe(COLUMN LIKE '%pattern%',.95) or (as percent using integer value in 0..100) maybe(COLUMN LIKE '%pattern%',95) with a default value of (possibly) 50% (or .5) for the optional second arg? -Original Message- From: Richard Hipp Sent: Tuesday, September 10, 2013 10:26 PM

Re: [sqlite] Hints for the query planner

2013-09-10 Thread James Berry
On Sep 10, 2013, at 12:26 PM, Richard Hipp wrote: > SURVEY QUESTION: > > The question for today is what to call this magic hint function: > > (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) > > Please feel

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Marc L. Allen
As I was reading this, I said to myself, "what they really need is a confidence value." Then I read the end and, there it was! A confidence value. Ok.. not exactly confidence, but I think you get my meaning. It seems to me that you're allowing the query writer to substitute personal

Re: [sqlite] Hints for the query planner

2013-09-10 Thread kyan
Hello Dr Hipp, First of all, I apologize for this rather off-topic suggestion knowing that you may have already implemented the syntax you describe, but there is an IMHO good reason for it, read ahead. On Tue, Sep 10, 2013 at 10:26 PM, Richard Hipp wrote: > SELECT DISTINCT

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Keith Medcalf
How about three, where two are just overloaded, or rather syntactic sugar, for the main declaration: unlikely(expr) likely(expr) likelihood(expr, rate) Where unlikely(expr) -> likelihood(expr, 0.05) likely(expr) -> likelihood(expr, 0.95) I would presume that the rate is the expected

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Simon Slavin
On 10 Sep 2013, at 10:48pm, Tim Streater wrote: > likelihood (EXPR, value) Best I've seen so far. I know it makes no sense without the second parameter but I think if you're going to make use of a special non-standard optimisation system you can be expected to know

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Stephan Beal
Or... unlikely(expr (, prob=0.05)) ==> likelihood (...) (sent from a mobile device - please excuse brevity, typos, and top-posting) - stephan beal http://wanderinghorse.net On Sep 11, 2013 12:51 AM, "Stephan Beal" wrote: > Plus an overload: unlikely(expr) ==>

Re: [sqlite] Hints for the query planner

2013-09-10 Thread David de Regt
Seconded. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Tuesday, September 10, 2013 3:46 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Hints for the query planner On 10 Sep 2013

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Stephan Beal
Plus an overload: unlikely(expr) ==> likelihood (expr, 0.05) (sent from a mobile device - please excuse brevity, typos, and top-posting) - stephan beal http://wanderinghorse.net On Sep 10, 2013 11:49 PM, "Tim Streater" wrote: > On 10 Sep 2013 at 20:26, Richard Hipp

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Tim Streater
On 10 Sep 2013 at 20:26, Richard Hipp wrote: > SURVEY QUESTION: > > The question for today is what to call this magic hint function: > > (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) > > Please feel free to

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Scott Robison
I think I prefer something along the lines of "unlikely" or "likely". The problem with a term like "selective" (at least in my brain) is that it doesn't imply (for the single argument version) in what way it is being selective. If a negative form of the magic function is used ("unlikely",

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Eric Minbiole
> (1) unlikely(EXPR) > (2) selective(EXPR) > (3) seldom(EXPR) > (4) seldom_true(EXPR) > (5) usually_not_true(EXPR) > > I quite like (2) "selective". I think it's reasonably descriptive on its own, and also works well with the optional second argument.

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Roman Fleysher
In Bayesian statistics there is a term "prior", prior probability. From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Richard Hipp [d...@sqlite.org] Sent: Tuesday, September 10, 2013 3:26 PM To: General Discussion of

Re: [sqlite] Hints for the query planner

2013-09-10 Thread Philip Bennefall
Hi Richard, What about "probability" or "likelyhood"? This works in both the case where the likelyhood is great as well as when it is low. From the list you provided, I would pick "unlikely". Kind regards, Philip Bennefall - Original Message - From: "Richard Hipp"