pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (11+ messages)  latest in thread

Message-ID: <77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.com>
Permalink:  ../77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.com/
Also on:    postgresql.org/message-id/77a3b7c7-7c95-7230-cd12-a5e7d920a00e@gmail.com

 · 

reply

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