On Sat, Feb 20, 2016 at 7:37 PM, Tom Lane <tgl@xxxxxxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
