Subject: Re: [GENERAL] Why Postgres use a little memory on Windows.
To: Francisco Olarte <folarte@peoplecall.com>
References: 
 <CAJg-yaO8OtzqTxsusesqntPhxG=LDc9skQe3SaVWw30NuWTs5Q@mail.gmail.com>
 <56C8ACBE.60004@aklaver.com>
 <CA+bJJbwGTJecWKm0pzfHzVX7GzyrEZ-SBsETe6z9zq1E3EkObg@mail.gmail.com>
Cc: tuanhoanganh <hatuan05@gmail.com>,
 PostgreSQL <pgsql-general@postgresql.org>,
 pgsql-performance <pgsql-performance@postgresql.org>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <56C8DF6B.7050101@aklaver.com>
Date: Sat, 20 Feb 2016 13:49:31 -0800
User-Agent: Mozilla/5.0 (X11; Linux i686; rv:38.0) Gecko/20100101
 Thunderbird/38.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+bJJbwGTJecWKm0pzfHzVX7GzyrEZ-SBsETe6z9zq1E3EkObg@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance