On 02/20/2016 10:39 AM, Francisco Olarte wrote:
On Sat, Feb 20, 2016 at 7:13 PM, Adrian Klaver
<adrian.klaver@xxxxxxxxxxx> 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@xxxxxxxxxxx
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
