On Sat, Feb 20, 2016 at 7:13 PM, Adrian Klaver
<adrian.klaver@aklaver.com> wrote:
.....
> FROM
> sym_data d INNER JOIN sym_data_gap g ON g.status = 'GP'
> AND d.data_id BETWEEN g.start_id
> AND g.end_id
.....
> The thing that stands out to me is that I do not see that sym_data and
> sym_data_gp are actually joined on anything.
Yes they are, although the formatting hid it somehow.
It is a classic, data_gap defines intervals via start+end id over
data, he wants to join every data with the corresponding gap. It is a
hard optimization problem without knowing more of the data
distributions, maybe the interval types and ginindexes can help him.
When faced with this kind of structure, depending on the data
distribution, I've solved it via two paralell queries ( gap sorted by
start plus end, data sorted by id, sweep them in paralell joining by
code, typical tape-update problem, works like a charm for
non-overlapping ranges and even for some overlapping ones with a
couple of queues ) . And he seems to want all of the data ( sometime
this goes faster if you can add a couple of range conditions for
data.id / gap.start/end_id.
> Also is it possible to see the schema definitions for the two tables?
My bet is on somethink like data.id ~serial primary key,
gap.start/end_id foreign key to that.
Francisco Olarte.