Re: need suggestion on querying big tables

Holger Jakobs <holger@xxxxxxxxxx> · Mon, 5 Dec 2022 08:35:07 +0100



    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:
    
      make sure that the join operation uses indexes
      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
  

Attachment:
OpenPGP_signature

Description: OpenPGP digital signature