From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Francisco Olarte <folarte@peoplecall.com>
Cc: tuanhoanganh <hatuan05@gmail.com>
Cc: PostgreSQL <pgsql-general@postgresql.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [GENERAL] Why Postgres use a little memory on Windows.
Date: Sat, 20 Feb 2016 13:49:31 -0800
Message-ID: <56C8DF6B.7050101@aklaver.com> (raw)
In-Reply-To: <CA+bJJbwGTJecWKm0pzfHzVX7GzyrEZ-SBsETe6z9zq1E3EkObg@mail.gmail.com>
References: <CAJg-yaO8OtzqTxsusesqntPhxG=LDc9skQe3SaVWw30NuWTs5Q@mail.gmail.com>
<56C8ACBE.60004@aklaver.com>
<CA+bJJbwGTJecWKm0pzfHzVX7GzyrEZ-SBsETe6z9zq1E3EkObg@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adrian.klaver@aklaver.com, folarte@peoplecall.com, hatuan05@gmail.com, pgsql-general@postgresql.org
Subject: Re: [GENERAL] Why Postgres use a little memory on Windows.
In-Reply-To: <56C8DF6B.7050101@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox