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)
