Re: need suggestion on querying big tables

Partitioning the table if possible should also help.

Cheers,

*Bo Guo*

On Mon, Dec 5, 2022 at 2:35 AM Holger Jakobs <holger(at)jakobs(dot)com> wrote:

> 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
>
>