On 02/20/2016 10:39 AM, Francisco Olarte wrote:
> 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.
Thanks to you and Tom for enlightening me. I am going to have to spend
some time puzzling this out to convert what you have shown into
something that I can wrap my head around.
>
>> 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.
>
--
Adrian Klaver
adrian.klaver@aklaver.com
