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)
