Re: need suggestion on querying big tables

[Ron <ronljohnsonjr@xxxxxxxxx>]

    On 12/5/22 00:29, Ebin Jozer wrote:

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

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

--
Angular momentum makes the world go 'round.