Skip site navigation (1) Skip section navigation (2)

Re: tricky query

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "John A Meinel" <john(at)arbash-meinel(dot)com>
Cc: "Postgresql Performance" <pgsql-performance(at)postgresql(dot)org>,"elein" <elein(at)varlena(dot)com>
Subject: Re: tricky query
Date: 2005-06-28 16:02:09
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3415C2BFF@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
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
had given up on it.  I think your solution (smallest X1 not in X) is a
good candidate for general bits, so I'm passing this to varlena for
review :)

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;

Merlin

Responses

pgsql-performance by date

Next:From: John A MeinelDate: 2005-06-28 16:25:56
Subject: Re: tricky query
Previous:From: John A MeinelDate: 2005-06-28 15:43:25
Subject: Re: tricky query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group