From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, tuanhoanganh <hatuan05(at)gmail(dot)com>, PostgreSQL <pgsql-general(at)postgresql(dot)org>, pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] Why Postgres use a little memory on Windows. |
Date: | 2016-02-20 18:58:14 |
Message-ID: | CA+bJJbysJLGS07zr60r+B0OxtEuckvdm-vYt7_3rz2=emaAs7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-performance |
On Sat, Feb 20, 2016 at 7:37 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> It looks like the bitmap heap scan generally returns exactly one row for
> each outer row, which makes me wonder if the BETWEEN couldn't be replaced
> with some sort of equality.
Mm, I'm not good reading explains, but that seems to confirm my
suspicion that gaps partition the id range in non overlapping ranges.
> But that might take some rethinking of the data.
If id is a series, gap defines a range, he can do something with an
auxiliary table, like
select start as a, 0 as b from gaps where status = 'GP'
union all
select id as a,1 as b from data
union all end-1 as a, 2 as b from gaps where status='gp' -- to end-1
to make intervals half open.
order by a,b
which would give all the ids in a with b=1 surrounded by (0,2) when
valid and by (2,0) when invalid.
and then, with a creative window clause or a small function, filter
that and join with data.id. I suppose adding a third c column, null on
b=1 and =b on b=0/2 and selecting the previous non-null in the
sequence could do it, but it's somehow above my window-fu, I'm more of
a code gouy and would do it with two nested loops on a function.
Francisco Olarte.
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-02-20 21:49:31 | Re: [GENERAL] Why Postgres use a little memory on Windows. |
Previous Message | Francisco Olarte | 2016-02-20 18:39:23 | Re: Why Postgres use a little memory on Windows. |
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2016-02-20 21:49:31 | Re: [GENERAL] Why Postgres use a little memory on Windows. |
Previous Message | Francisco Olarte | 2016-02-20 18:39:23 | Re: Why Postgres use a little memory on Windows. |