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 1rO5DH-001A4m-5l for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 00:15:55 +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 1rO5DF-00GHOz-Nh for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 00:15:53 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rO5DF-00GHOr-DB for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 00:15:53 +0000 Received: from mail-oa1-x32.google.com ([2001:4860:4864:20::32]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rO5D8-0016Cm-Hn for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 00:15:52 +0000 Received: by mail-oa1-x32.google.com with SMTP id 586e51a60fabf-203fe0e3fefso2760841fac.2 for ; Thu, 11 Jan 2024 16:15:46 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705018544; x=1705623344; darn=lists.postgresql.org; h=content-transfer-encoding:in-reply-to:from:references:cc:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=Eko/evE+CrO/oTe3t1IDU/cWI18v3IebIJufN6CWkO0=; b=CHf7U0CY3YvLSm9E38uOxofKNMhhmhjwQVSiVBid8cqYWFLPoMwIIl18aw1qzDz8LF 9D0DKfquLptS+aZPVgv4rCqahgbvmrt6aHD6Ye536rKJ+SjwXF+0SUGcy1mgvEvpi7xO 58PDg73sAfMZkP+JVGDhAq5yj+p2ptun7AfAVeW4iK4rPkaVYpm80Vqjs4WU1gGHYm7z XDgwmjCY5yCJf9bf4ZqWNOeqQgxzIuHe6lTKyVtbr8ZXMkT7zR5/8Np70TN5BVcdPpEz i1eWpHwBjzbexH93lDsKUZ5jYXGyiBWow9+gMr4dX2hBcfYR5OkCmCYUc6vbbutqqPYa uVWw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705018544; x=1705623344; h=content-transfer-encoding:in-reply-to:from:references:cc: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=Eko/evE+CrO/oTe3t1IDU/cWI18v3IebIJufN6CWkO0=; b=qhoE6QEZvM4wVaqeOjqdwlSxZV3MFn8+AIbUS0Dcl3q9Miu7WtGGfhXEW1ZGLbbV36 rBOYcy4/ry0H4q+mSywZDdyuQ5Wy5TrJC+Wn8Tm1ZRPwCUzZNMWDSIYYkQSL66F/GHS0 oiCY8wIdw2uJFgrsl+bQkct0xlH4bgO0RKtUaAqXkycVU6cQZXGaBurb6GQQx8Uok08Y is6l/snOsWivI4H5YfRaUQveIVne2oGcDEJVoSn0zoMAWjBODcYlrrx3aR69zUm00rx/ vWP7rEiKLCh7Qq+3aPEHLjXAreuATEPrciXMqZGyKf23HAMD92LJ5x0Dyo7TB7LvdsDw J5KQ== X-Gm-Message-State: AOJu0Yyk9sKRf5APL+wgsZo7ri0O3AZSFbfKpPKExpXMyAS8ly9ZVjtG GRK4nY0Cv3z5hC04wptnVQ== X-Google-Smtp-Source: AGHT+IEADX5MPGoiM+YBA5yPb1F/gYOW/MPy1R+Ep6ovT36I7dE5ASP9GM09wHYB45FHSi7U7UB9dA== X-Received: by 2002:a05:6870:a118:b0:206:941a:56b8 with SMTP id m24-20020a056870a11800b00206941a56b8mr585511oae.87.1705018544155; Thu, 11 Jan 2024 16:15:44 -0800 (PST) Received: from [192.168.2.15] (070-113-014-216.res.spectrum.com. [70.113.14.216]) by smtp.gmail.com with ESMTPSA id xe11-20020a056870ce8b00b002064d200143sm498793oab.12.2024.01.11.16.15.43 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 11 Jan 2024 16:15:43 -0800 (PST) Message-ID: <3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com> Date: Thu, 11 Jan 2024 18:15:41 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Refresh Materialized View Issue Content-Language: en-US To: Ron Johnson , Jeremiah Bauer Cc: "pgsql-general@lists.postgresql.org" References: From: Jim Nasby In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 1/11/24 3:40 PM, Ron Johnson wrote: > On Thu, Jan 11, 2024 at 3:50 PM Jeremiah Bauer > wrote: > > My question is: what indexes are on public.large_table? > Hopefully there's a compound b-tree index on id1, id2, id3. > > There is not, after further investigation.  There are these 4 > indexes that involve id1, id2, and id3.  Should I try creating an > index on all three of the columns? > > CREATE INDEX IF NOT EXISTS idx_large_table_id1 > > [snip] > > CREATE INDEX IF NOT EXISTS idx_large_table_id2 > > [snip] > > CREATE INDEX IF NOT EXISTS idx_large_table_id3 > > [snip] > > CREATE INDEX IF NOT EXISTS idx_large_table_id2_id3 > > [snip] > I'd strongly think about creating such an index, since the current > indices don't help much. That'd be a band-aid at best, because we know that the query used to define the materialized view runs in a reasonable amount of time on it's own, as does a CTAS. So either the REFRESH is doing something odd when writing into the new relation (which looking at the code seems very unlikely), or REFRESH is getting a different query plan for some reason. Unfortunately, I don't know of any easy way to get the query plan for the REFRESH (it might be possible via gdb, but I'm not sure). We do at least know that the REFRESH is using parallel workers. Can you post the output of EXPLAIN ANALYZE for the SELECT? That might provide some clues. -- Jim Nasby, Data Architect, Austin TX