Subject: Re: need suggestion on querying big tables
To: pgsql-admin@lists.postgresql.org
References: 
 <CAJuN1jj6NQDUjbMH=n4zry10JMJEZ8bRi=j6uA-3_108-Tq9cQ@mail.gmail.com>
 <d926084d-63a7-854b-2d8d-459d33ec7f70@gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <dcf18d66-a6ab-40ad-31dc-b18ef07be337@gmx.net>
Date: Tue, 6 Dec 2022 15:59:16 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: <d926084d-63a7-854b-2d8d-459d33ec7f70@gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:ZcmioymvfPw=;xIuG2q8oMayJJpn2gCb2U5CxBuZ
 PrDlKlk/u52RUSSHDlmigzcGC5113guxO3knMUWIvXiyRgmXWSnNMBDQ/psMEHjrp9BCr5LG5
 UDS3MZ5VwHQlDCkEe7uX6PU60gX0oPUPNlX2+7w0ui4MnUqbeTjw6YPYYVdiiUKPPMjozMLth
 qRjWhciie8y/PZcTinCz5l+0eAU2+pIR/l3GUTctqjh38tuhaI28GQUpS5Y9zC8SUCg7tGyU6
 k34/3ggsvbbX8wkcYZKn54dFk1WsPIoeUwEFyErO0j1yAP6cr8Sl3EWCodT150fuqf3aS5LFB
 bKVhOV6o1+s62cdilSLNN2EK8S7+BKayK75kkB5UqQBg7KI1dlhG2T4vAP1qordxPopKetiOE
 XkbSZUrxvrh6HRS7CkARc8vy/s+cUegjlbUiKJeVYIYM+t5Wy1hWqzQWG7rHmz1DhumnDdyUm
 E3EKG54hCNWUITF7ftq7YzjPOiJpBzC1YMNcAp6fDVCVFpZJm+Z6E9tu1ramQWwWGntCIvxk3
 OgoIRm3FXWYBySe80qg4ThN+HeFXmVhYKKa9uZit4OTaO+V1pz87PoJXKGALOWfYC5HCeQIeA
 ldV98WhwlYeUtgS6JYKGkeqZ5NJgWHJvcNN3X0TEZQoCJlTruOwtGcchUJ6YDVIX0WPjzoG3g
 i0gCmtiOUx8l255xjS5XCksdIdyyN0vDTfuFjy2461JvUv6HPCOc/W5kyxJ6hIY1GQ6o/UpZz
 ASeteOeAUI8ovbzGNWMzelbQshR36DcGm9QPQYJwr/CZ/nrSeWgldsO0QpYU7Ae3Z3eHH5tT2
 9TdIZJ55ujn06ImJ0PwFJyAmmeGbsFN6dgBLAFXc7nbaR9a8w22lTv8545RekLmuJkMdirR/t
 5hMQ/bpBfxnp2bJM60dR8mu4EpiT36Eho0ECrMCR4Yu8V/TC5BgLlCDIxQFJDq/KoMpCG0IDA
 a/FVoKUNP+XdeMt+c9Ng9+009FA=
Archived-At: 
 <https://www.postgresql.org/message-id/dcf18d66-a6ab-40ad-31dc-b18ef07be337%40gmx.net>
Precedence: bulk

Ron schrieb am 06.12.2022 um 15:51:
>> 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??
>
> https://www.postgresql.org/docs/11/runtime-config-query.html
>
> effective_cache_size is how much RAM *a single* query gets.  "When
> setting this parameter you should consider both *PostgreSQL's shared
> buffers*".
>
> https://www.postgresql.org/docs/11/runtime-config-resource.html
>
> "a reasonable starting value for *shared_buffers is 25% of the
> memory* in your system. There are some workloads where even larger
> settings for shared_buffers are effective, but because PostgreSQL
> also relies on the operating system cache, *it is unlikely that an
> allocation of more than 40% of RAM to shared_buffers will work
> better* than a smaller amount."
>
> You've set effective_cache_size to 83%, when it should be at most
> 20%.


effective_cache_size does not allocate any memory.
It's not a setting that controls "how much memory a query gets"

effective_cache_size merely a hint to the optimizer on the expected amount
of data that is likely to be in the cache.
Either the file system cache or shared_buffers.

The current setting of 12GB for shared_buffers is absolutely inside the
recommended limits. (40% would be about 25GB)