From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | John R Pierce <pierce(at)hogranch(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: dumb question |
Date: | 2016-06-02 20:23:41 |
Message-ID: | CAKFQuwbtJ67i0Oxn+-mgj5zPsh8jzjQ0_ntOEndw-bshxkTR1Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 2, 2016 at 4:11 PM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 6/2/2016 11:10 AM, Steve Clark wrote:
>
> Thanks all the below seem to do the trick.
>
> On 06/02/2016 01:58 PM, David G. Johnston wrote:
>
> select max(id) from yourtable where sts=0 and id not in (select ref_id
> from yourtable);
>
>
> select max(id) from yourtable where sts=0 and id not in (select ref_id
> from yourtable);
>
>
> do note, this is whats known as an 'anti-join', and these can be pretty
> expensive on large tables.
>
+1
Though I suspect that with a partial index on (id, sts=0) and (ref_id,
ref_id IS NOT NULL), though highly sensitive to density, that even for
large total row counts it would perform pretty well; but I'm not
knowledgeable in how smart we are here. Selecting, in descending order,
(id where sts = 0), from the index and then poking into index(ref_id)
should, particularly if the cross-set is sparse, pretty quickly find a
non-match.
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2016-06-02 20:29:40 | Re: WAL's listing in pg_xlog by some sql query |
Previous Message | David G. Johnston | 2016-06-02 20:16:13 | Re: dumb question |