From: | Holger Jakobs <holger(at)jakobs(dot)com> |
---|---|
To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
Subject: | Re: need suggestion on querying big tables |
Date: | 2022-12-05 07:35:07 |
Message-ID: | ec145210-d879-df91-ebc8-8ad2f5643e4b@jakobs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Bo Guo | 2022-12-05 12:14:05 | Re: need suggestion on querying big tables |
Previous Message | Ebin Jozer | 2022-12-05 06:29:21 | need suggestion on querying big tables |