Re: [PERFORM] tricky query

2005-06-28 Thread Dawid Kuroczko
On 6/28/05, John A Meinel <[EMAIL PROTECTED]> wrote: > Actually, if you already have a lower bound, then you can change it to: > > SELECT t1.id+1 as id_new FROM id_test t1 > WHERE t1.id > id_min > AND NOT EXISTS > (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) > ORDE

Re: [PERFORM] tricky query

2005-06-28 Thread Sebastian Hennebrueder
John A Meinel schrieb: > John A Meinel wrote: > >> > > Well, I was able to improve it to using appropriate index scans. > Here is the query: > > SELECT t1.id+1 as id_new FROM id_test t1 >WHERE NOT EXISTS >(SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) >ORDER BY t1.id LIMIT 1;

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
Cosimo wrote: > I'm very interested in this "tricky query". > Sorry John, but if I populate the `id_test' relation > with only 4 tuples with id values (10, 11, 12, 13), > the result of this query is: > >cosimo=> create table id_test (id integer primary key); >NOTICE: CREATE TABLE / PRIMAR

Re: [PERFORM] tricky query

2005-06-28 Thread Bruno Wolff III
On Tue, Jun 28, 2005 at 12:02:09 -0400, Merlin Moncure <[EMAIL PROTECTED]> wrote: > > Confirmed. Hats off to you, the above some really wicked querying. > IIRC I posted the same question several months ago with no response and > had given up on it. I think your solution (smallest X1 not in X)

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
> On Tue, Jun 28, 2005 at 12:02:09 -0400, > Merlin Moncure <[EMAIL PROTECTED]> wrote: > > > > Confirmed. Hats off to you, the above some really wicked querying. > > IIRC I posted the same question several months ago with no response and > > had given up on it. I think your solution (smallest X1

Re: [PERFORM] tricky query

2005-06-28 Thread Cosimo Streppone
John A Meinel wrote: John A Meinel wrote: Merlin Moncure wrote: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values 1,2,3,4,6 and 7, I need a query that returns the value of 5. >> [...] > Well, I was able

Re: [PERFORM] tricky query

2005-06-28 Thread John A Meinel
Merlin Moncure wrote: On Tue, Jun 28, 2005 at 12:02:09 -0400, Merlin Moncure <[EMAIL PROTECTED]> wrote: Confirmed. Hats off to you, the above some really wicked querying. IIRC I posted the same question several months ago with no response and had given up on it. I think your solution

Re: [PERFORM] tricky query

2005-06-28 Thread Sam Mason
John A Meinel wrote: >SELECT t1.id+1 as id_new FROM id_test t1 > WHERE NOT EXISTS > (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) > ORDER BY t1.id LIMIT 1; This works well on sparse data, as it only requires as many index access as it takes to find the first gap. The simpler "NO

Re: [PERFORM] tricky query

2005-06-28 Thread John A Meinel
Merlin Moncure wrote: John Meinel wrote: See my follow up post, which enables an index scan. On my system with 90k rows, it takes no apparent time. (0.000ms) John =:-> Confirmed. Hats off to you, the above some really wicked querying. IIRC I posted the same question several months ago wi

Re: [PERFORM] tricky query

2005-06-28 Thread Cosimo Streppone
Merlin Moncure wrote: I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. I've already worked out a query using generate_series (not scalable) and pl/pgsql. An SQL only solution would

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
> Merlin Moncure wrote: > > > I need a fast way (sql only preferred) to solve the following problem: > > I need the smallest integer that is greater than zero that is not in the > > column of a table. > > > > I've already worked out a query using generate_series (not scalable) and > > pl/pgsql. A

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
John Meinel wrote: > See my follow up post, which enables an index scan. On my system with > 90k rows, it takes no apparent time. > (0.000ms) > John > =:-> Confirmed. Hats off to you, the above some really wicked querying. IIRC I posted the same question several months ago with no response and ha

Re: [PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
> Not so bad. Try something like this: > > SELECT min(id+1) as id_new FROM table > WHERE (id+1) NOT IN (SELECT id FROM table); > > Now, this requires probably a sequential scan, but I'm not sure how you > can get around that. > Maybe if you got trickier and did some ordering and limits. The a

Re: [PERFORM] tricky query

2005-06-28 Thread John A Meinel
John A Meinel wrote: Merlin Moncure wrote: I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values 1,2,3,4,6 and 7, I need a query that retu

Re: [PERFORM] tricky query

2005-06-28 Thread Sam Mason
Merlin Moncure wrote: >I've already worked out a query using generate_series (not scalable) and >pl/pgsql. An SQL only solution would be preferred, am I missing >something obvious? I would be tempted to join the table to itself like: SELECT id+1 FROM foo WHERE id > 0 AND i NOT IN (SELE

Re: [PERFORM] tricky query

2005-06-28 Thread John A Meinel
Merlin Moncure wrote: Not so bad. Try something like this: SELECT min(id+1) as id_new FROM table WHERE (id+1) NOT IN (SELECT id FROM table); Now, this requires probably a sequential scan, but I'm not sure how you can get around that. Maybe if you got trickier and did some ordering and

Réf. : [PERFORM] tricky query

2005-06-28 Thread bsimon
16:21 Pour : cc : Objet : [PERFORM] tricky query I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values

Re: [PERFORM] tricky query

2005-06-28 Thread John A Meinel
Merlin Moncure wrote: I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values 1,2,3,4,6 and 7, I need a query that returns the value of 5. I'

Re: [PERFORM] tricky query

2005-06-28 Thread Bruno Wolff III
On Tue, Jun 28, 2005 at 10:21:16 -0400, Merlin Moncure <[EMAIL PROTECTED]> wrote: > I need a fast way (sql only preferred) to solve the following problem: > > I need the smallest integer that is greater than zero that is not in the > column of a table. In other words, if an 'id' column has valu

[PERFORM] tricky query

2005-06-28 Thread Merlin Moncure
I need a fast way (sql only preferred) to solve the following problem: I need the smallest integer that is greater than zero that is not in the column of a table. In other words, if an 'id' column has values 1,2,3,4,6 and 7, I need a query that returns the value of 5. I've already worked out a q