Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUT6i-00Cyov-GF for pgsql-general@arkaria.postgresql.org; Fri, 11 Aug 2023 14:27:16 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qUT6h-008Pcg-1i for pgsql-general@arkaria.postgresql.org; Fri, 11 Aug 2023 14:27:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qUT6g-008PcF-N2 for pgsql-general@lists.postgresql.org; Fri, 11 Aug 2023 14:27:15 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qUT6e-001oKf-Od for pgsql-general@lists.postgresql.org; Fri, 11 Aug 2023 14:27:13 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-583ae4818c8so21894507b3.3 for ; Fri, 11 Aug 2023 07:27:12 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1691764032; x=1692368832; h=content-transfer-encoding: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=dG8xTu6o1ScuZitOGm62sTOaZ+WNRCTTzKlv8NhUSNg=; b=hMDTnuS1ElTc+bf7mGockwlxyCrfi4Hwds+2x3mNLkWglI3CHI29EPdA+PJXYNGTve rmDEeHUtHVVYwD9bCNNWVVy92c0IqK6N6/lHR8bcWNhNzFbkYvWdLLHnqtyRFaEy6NyP N/WUVgaxbX1HxX3e6xlFN4GubRhs68LtGNzBJCfOMOBBQONIDyUunGmGRG/eLO17734R 2oXRF7zdQ9FoWMl8v6827AfErKZxSZOTT2OuICEkeJ4cpIJrnKxrm9kJQ9o2ucut3hun /PqYVDYcuspYlvDyunTgjse0N/Sq3dgun7c/ZnoojrW3s3puqs0uBRpspQk7WGIZETtH F7vw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1691764032; x=1692368832; h=content-transfer-encoding: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=dG8xTu6o1ScuZitOGm62sTOaZ+WNRCTTzKlv8NhUSNg=; b=Yuy74fPNUnmn6SHnt50YHmRLHw7dZ0hawI1Y+Lq5swt+Y4n6zGB8uB+je9za2lcZDM xrGS8yJNVUwz2KX/c0DqpSSLjPEPHGsQTa0rDLvwn7b+93IxIYdqVH6B8vS7VkOJFpmT zSwo/zvWpzJ61nfTZg7Oud++bkNzOZLg6DEjuGKAKnWZP9X/+onX2y9ENoOcRzYyVbIo SEtCXWbb8//AsNYekGK1KOeHx8sri3snF5gbPozfggq/zdRhJLtH8w9/yLzdaisTZ8C6 ZkwyJ0uHeabpWEluPlhhhew5BPZnTGL/10NZU0Bf6YGjSEHc/BMgrfHqotaLreAGSqld I1/w== X-Gm-Message-State: AOJu0Yz/QDmnBD3pIipCbwkkfWW5ErR19rPnzt3fl9HKQtx1O2PEQUzi VfwBUcsgEslJhXovCvby/p3o8zr2f2I= X-Google-Smtp-Source: AGHT+IHqCJiWT972LPChsGemWatgBizufSRq/xmB5g7gd8kDZwJTw/gnEpJcC1pBbCuXmD83MpWANQ== X-Received: by 2002:a0d:fa41:0:b0:589:8b55:fe09 with SMTP id k62-20020a0dfa41000000b005898b55fe09mr1900227ywf.50.1691764031910; Fri, 11 Aug 2023 07:27:11 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:f436:eae9:e4be:4f1? ([2600:1700:bd2c:b810:f436:eae9:e4be:4f1]) by smtp.googlemail.com with ESMTPSA id m6-20020a817106000000b00586b2198ffasm1013283ywc.139.2023.08.11.07.27.11 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 11 Aug 2023 07:27:11 -0700 (PDT) Message-ID: <7fad2860-2eff-d3b4-7797-c6d5821e0f01@gmail.com> Date: Fri, 11 Aug 2023 09:27:11 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: pb with big volumes Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <72a34a05-47cb-1635-e56a-79d1f7082094@gmail.com> From: Ron In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 8/10/23 23:40, David Rowley wrote: > On Fri, 11 Aug 2023 at 13:54, Ron wrote: >> Wouldn't IO contention make for additive timings instead of exponential? > No, not necessarily. Imagine one query running that's doing a > parameterised nested loop join resulting in the index on the inner > side being descended several, say, million times. Let's say there's > *just* enough RAM/shared buffers so that the index pages, once the > index is scanned the first time, all the required pages are cached > which results in no I/O on subsequent index scans. Now, imagine > another similar query but with another index, let's say this index > also *just* fits in cache. Now, when these two queries run > concurrently, they each evict buffers the other one uses. Of course, > the shared buffers code is written in such a way as to try and evict > lesser used buffers first, but if they're all used about the same > amount, then this can stuff occur. The slowdown isn't linear. But that's cache thrashing (which was OP's concern), not IO contention. -- Born in Arizona, moved to Babylonia.