pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: need suggestion on querying big tables
Date: Mon, 5 Dec 2022 08:35:07 +0100
Message-ID: <ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.com> (raw)
In-Reply-To: <CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com>
References: <CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com>

Am 05.12.22 um 07:29 schrieb Ebin Jozer:
> Hi Team,
> in postgresql 11 version we have two tables of size 435 GB and 347 GB.
> if we query on single table or if we are doing inner join on both the 
> big tables, it is not displacing any output, it keeps running
>
> We can see the wait event is IO and directDatafile .
>
> Server Spec : 8 cores and  64GB RAM
> PG config : 53 GB(effective_cache), 12 GB(shared buffer)
>
> can you please suggest some ideas , how we can query on big tables and 
> fasten them to get the output??
>
> Thanks & Regards,
> /Ebin/

There are basically two "secrets" to performance in this case:

 1. make sure that the join operation uses indexes
 2. make sure that you have enough work_mem available

work_mem can be set in postgresql.conf, but also in every session 
individually. So don't set it too high generally, because it will be 
allocated for every sorting and hashing operation.

Kind Regards,

Holger

-- 
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012



Attachments:

  [application/pgp-signature] OpenPGP_signature (202B, ../ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.com/3-OpenPGP_signature)
  download

view thread (15+ messages)  latest in thread

Message-ID: <ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.com>
Permalink:  ../ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.com/
Also on:    postgresql.org/message-id/ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.com

copy link · copy postgr.es

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 using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: holger@jakobs.com, pgsql-admin@lists.postgresql.org
  Subject: Re: need suggestion on querying big tables
  In-Reply-To: <ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.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