On Sat, Feb 20, 2016 at 7:37 PM, Tom Lane <tgl@sss.pgh.pa.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.