From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: bug or lacking doc hint
Date: Mon, 26 Jun 2023 09:05:31 -0500
Message-ID: <77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.com> (raw)
In-Reply-To: <CADX_1abEKKW8SSYjB_Pe3EZyBkX2dG3SXX1w6tXxrnve5GWBxw@mail.gmail.com>
References: <CADX_1abJasPxOFSxDm+XVU3dZSUr5wVBz_xFh7tKcdBDqeozsw@mail.gmail.com>
<CAApHDvo-P9b0jDa+CKkG6_+budNn8QQhy47YF5i_ipwCBr6XrA@mail.gmail.com>
<587381.1687729675@sss.pgh.pa.us>
<CADX_1abrHNz1pN1JvRLCMSopMsRWyKfuWVDneSKxGH0A5KOfEw@mail.gmail.com>
<CAN5h1oQWOCbFFjqbK9d7xA5uZ8ooKjabJmRdAhnRWYSCz9jKSw@mail.gmail.com>
<CADX_1abEKKW8SSYjB_Pe3EZyBkX2dG3SXX1w6tXxrnve5GWBxw@mail.gmail.com>
On 6/26/23 07:22, Marc Millas wrote:
>
>
> On Mon, Jun 26, 2023 at 5:47 AM Avin Kavish <avin@baseboard.ai> wrote:
>
> Sounds like the problem you are having is, the server is running out
> of temporary resources for the operation that users are trying to do.
> So according to Tom, on the postgres side, the operation cannot be
> optimized further.
>
> I think you have few choices here,
> - See if increasing the resources of the server will allow them to
> run the operation
> - Ask users not to do that operation
> - Use a extension like citus to scale horizontally
>
> But I'm thinking why a massively inefficient join is needed in the
> first place. Shouldn't joins be for following keyed relationships. So
> ideally a unique indexed column, but at the very least an indexed
> column. Why is a join required on a dynamically calculated substring?
> Can it be made into a static computed value and indexed? Substring
> sounds like an op that should be in the filter stage.
>
> Can you describe your data model? Maybe we can give some specific advice.
>
>
> There is a set of big tables containing fine grain health data. The DB is
> accessed by people doing research on various kind of sicknesses. So, by
> nature, all columns (I mean ALL) can be used for every kind of SQL
> including, obviously, lots of intricated joins.
This sounds like the kind of problem normally solved by data warehouses. Is
your schema designed like a DW, or is it in 3NF?
--
Born in Arizona, moved to Babylonia.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: bug or lacking doc hint
In-Reply-To: <77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.com>
References: <CADX_1abJasPxOFSxDm+XVU3dZSUr5wVBz_xFh7tKcdBDqeozsw@mail.gmail.com> <CAApHDvo-P9b0jDa+CKkG6_+budNn8QQhy47YF5i_ipwCBr6XrA@mail.gmail.com> <587381.1687729675@sss.pgh.pa.us> <CADX_1abrHNz1pN1JvRLCMSopMsRWyKfuWVDneSKxGH0A5KOfEw@mail.gmail.com> <CAN5h1oQWOCbFFjqbK9d7xA5uZ8ooKjabJmRdAhnRWYSCz9jKSw@mail.gmail.com> <CADX_1abEKKW8SSYjB_Pe3EZyBkX2dG3SXX1w6tXxrnve5GWBxw@mail.gmail.com> <77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.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