Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p2CiX-0002KN-Ih for pgsql-admin@arkaria.postgresql.org; Mon, 05 Dec 2022 14:45:13 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p2CiW-0001K2-Ho for pgsql-admin@arkaria.postgresql.org; Mon, 05 Dec 2022 14:45:12 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p2CiW-0001Jt-7B for pgsql-admin@lists.postgresql.org; Mon, 05 Dec 2022 14:45:12 +0000 Received: from mail-oi1-x22a.google.com ([2607:f8b0:4864:20::22a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p2CiT-0002ZI-Tp for pgsql-admin@lists.postgresql.org; Mon, 05 Dec 2022 14:45:11 +0000 Received: by mail-oi1-x22a.google.com with SMTP id s187so7049277oie.10 for ; Mon, 05 Dec 2022 06:45:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=kDa0xbjqJlUcEo8ySeV4I/u1jmtptJfjCbWy+Xffw/A=; b=Ql6EWhpUpH0vK/1QPcndsA/7H06K6lMKkO785KNVUaolM63vS6i9MKgX6O+H/vSLeX 8BKcOBH0+lhADoW5MA616EzBoyvvJo0JgF5zjPB/PsqUzoTN+J3UsFFW1BTbn9w28Z3u ctr3ZP3tTZ5xKbrA8bo8ALP0NcyNL8znGy4ymkg6pr5Ar7MJwjNYzXUWTI8YCCWErA2c Jpu/pefXyme7FssJxok94mmrL1su2fupk9ZhE961/T4Q6JNYNDg5/lTXAeRUiWlB4AVU aEr13p9rQAOD2A6YPqsz+ALVEs+weTigRAfcRI+gmvVian4Y+03WXi3XMpUrkCcEl+fa jKyg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=kDa0xbjqJlUcEo8ySeV4I/u1jmtptJfjCbWy+Xffw/A=; b=Lt4uXJeoETLEn45dUdtjxz+Xprz30VjvrbPUHPYh0Zd2Qpie9T2+YElQ4GvDCc/UUJ 7eSMTJy3L4tDiewQG8vMu0BYhQCOHsXx9urKO1FLNVbsjIxKozGGBusQUKuvV4xECWY9 2CMFAczB+7HXiVT4WLDKQAjlst/l7KzzUKx82ljy+/i5tgmjpEcyvIzFAcnQp431v0N2 ZMcNrxQr4FMSq/bmyE8f5H2cXO73duKuItkhvwG8wpEQp0WnDPFsKBBD/beshWjeK31f DBprbJSS3XBoQ3cCYH5R8K9Y5Pdg9gDgyBLVxvsdAPhhT6yYV6nWwQ9tKEZAqkSLuvr6 LsTA== X-Gm-Message-State: ANoB5pkNt3IVksI6kMU/vNelGivGAr43Xck2Ho29AW4N213bS1xlZIjg cfDguC41qpNPR8HPp2tsglYO+D5TvAg= X-Google-Smtp-Source: AA0mqf4RuLnYkflEmKOT8mFgDYBmPgG/ECgUAmyqj7W/OA8aIwErG9Z9RFpzvhb1zs2LUR9GND7Cqg== X-Received: by 2002:aca:6103:0:b0:35b:13ca:23a0 with SMTP id v3-20020aca6103000000b0035b13ca23a0mr32310660oib.12.1670251509153; Mon, 05 Dec 2022 06:45:09 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id cr5-20020a056830670500b006619533d1ddsm7722948otb.76.2022.12.05.06.45.08 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 05 Dec 2022 06:45:08 -0800 (PST) Content-Type: multipart/alternative; boundary="------------usdVUicqsx0kr0OdWIRR7lyf" Message-ID: <8c748c20-84bd-ed7b-c10f-35d640c6994b@gmail.com> Date: Mon, 5 Dec 2022 08:45:08 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: need suggestion on querying big tables Content-Language: en-US To: pgsql-admin@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------usdVUicqsx0kr0OdWIRR7lyf Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 12/5/22 01:35, Holger Jakobs wrote: > 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: > > 1. make sure that the join operation uses indexes > 2. 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. > A "just smart enough" user could perform an inadvertent Denial of Service attack on the database by cranking his session work_mem really high, no? -- Angular momentum makes the world go 'round. --------------usdVUicqsx0kr0OdWIRR7lyf Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 12/5/22 01:35, Holger Jakobs wrote:
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:

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


A "just smart enough" user could perform an inadvertent Denial of Service attack on the database by cranking his session work_mem really high, no?

--
Angular momentum makes the world go 'round.
--------------usdVUicqsx0kr0OdWIRR7lyf--