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 1rOMvx-003c0R-Ar for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 19:11:13 +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 1rOMvw-004m4Z-Fv for pgsql-general@arkaria.postgresql.org; Fri, 12 Jan 2024 19:11:12 +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 1rOMvw-004m4R-5T for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 19:11:12 +0000 Received: from mail-oo1-xc29.google.com ([2607:f8b0:4864:20::c29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rOMvt-001EyY-GA for pgsql-general@lists.postgresql.org; Fri, 12 Jan 2024 19:11:11 +0000 Received: by mail-oo1-xc29.google.com with SMTP id 006d021491bc7-5955a4a9b23so3569197eaf.1 for ; Fri, 12 Jan 2024 11:11:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1705086667; x=1705691467; darn=lists.postgresql.org; 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=YK5LORmuswU2h/cpSl2VYflChLDfShlkFl0yBLV1vns=; b=KmGvUlwR6NG9P0Ngh+E3of//LtZPeQCzr9bFaDkS4WHkm6VYFymwgXYWd/RJYUP1sh Lk1SRHOKfHpZh9nWIHiV86CJZxhEnh3n2vHCA+3tS20ho8nY+fUjZHBgqEXPK7ZPcT5H z7iQUJq9Hz8AXLXoRn8aoSY3YHykacZVZESXDG9TRvSIYVsZSuBq7iaw+FPvlMBoVgO/ XnPKewzfgTRIsSD/cbsxyMo8U+21SXtG6Pfz7dQj/QHx52LtIsyl4CUOOvWHZnRCl/dy OVlaXQatxzMLg/JVMEzkpZIqSeCvmb24dtvxV0Rb9ehQbipqUrQpIpGDiUYy51hUF6DX Tq6g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705086667; x=1705691467; 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=YK5LORmuswU2h/cpSl2VYflChLDfShlkFl0yBLV1vns=; b=ICLBDBRiL6SudQ0vDMTV1LlbIJxsScrkKlmK2aAY2VQnLGa1MonF+ZH0ErsbpmPfOP kXSIXJgkzN/4+9C/4MaZhfimTQwuwv9lgbMrQ2m1SwqI2ZRX84F1mow+QxnJqwQKe12B V82csQ2vbmrEcNpvL0s7Dht+3wg1MVrcsGUx/pUx/JqsZvuJtDUp4I2r/VQucTDn08j4 xIRAY62e3/6Sbhdct7i+AjxB7muYE7J6+o/wtcYNeLOV1NM8OAv5FGDLiZB7SDamBsJC 1qmD8nvWPdXTK+e1Gk24dPbkal3OUzAVohVX1Yaku5AI1V1xe23jOl5OBLKBbsEAHFvQ 92jg== X-Gm-Message-State: AOJu0YzReXfHRiDXv7ruE0JXW46XuJGWyWRa8OIaKUNHdQV5zEJVzaXO Amd8qUsmytpkSdQZ0/EjFz2sJQhydyvK X-Google-Smtp-Source: AGHT+IHshW4QVKwzfvRWQy/VGp5ImnF1a/8co5foMfYmHSgLk4l2DgUoXrZRozoBX7hQo4dYr38I7Q== X-Received: by 2002:a05:6820:1acc:b0:598:6ed0:4015 with SMTP id bu12-20020a0568201acc00b005986ed04015mr1648639oob.3.1705086667619; Fri, 12 Jan 2024 11:11:07 -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 c12-20020a4aaccc000000b0059560cc8da7sm785134oon.24.2024.01.12.11.11.07 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Fri, 12 Jan 2024 11:11:07 -0800 (PST) Message-ID: Date: Fri, 12 Jan 2024 13:11:05 -0600 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: [EXTERNAL]Re: Refresh Materialized View Issue Content-Language: en-US To: Jeremiah Bauer , "pgsql-general@lists.postgresql.org" References: <3a8e256d-707b-48f0-b71a-b721964bafa5@gmail.com> 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/12/24 6:34 AM, Jeremiah Bauer wrote: > > 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. > > Sure, here is the explain analyze for the select: > > HashAggregate  (cost=123986470.20..129486707.63 rows=164493082 width=24) > (actual time=697250.385..741548.965 rows=59015171 loops=1) >   Group Key: id1, id2, id3 >   Planned Partitions: 64  Batches: 65  Memory Usage: 328209kB  Disk > Usage: 6750176kB >   ->  Gather  (cost=64653301.50..107228737.47 rows=328986164 width=24) > (actual time=357598.331..594226.355 rows=161151623 loops=1) >         Workers Planned: 2 >         Workers Launched: 2 >         ->  HashAggregate  (cost=64652301.50..74329121.07 > rows=164493082 width=24) (actual time=357572.082..578038.457 > rows=53717208 loops=3) >               Group Key: id1, id2, id3 >               Planned Partitions: 64  Batches: 65  Memory Usage: > 328209kB  Disk Usage: 25774088kB >               Worker 0:  Batches: 65  Memory Usage: 328209kB  Disk > Usage: 25375784kB >               Worker 1:  Batches: 65  Memory Usage: 328209kB  Disk > Usage: 25382936kB >               ->  Parallel Seq Scan on large_table >  (cost=0.00..29740358.40 rows=685387840 width=24) (actual > time=12.954..99596.289 rows=548310252 loops=3) > Planning Time: 5.380 ms > Execution Time: 745750.371 ms Ok, so that's using a parallel query as well, just like REFRESH, so no help there. At this point I think you'd need to do some OS-level investigation using a tool like perf to determine what the parent process is doing once the workers finish. BTW, I did just come across a script[1] that will print the query plan for a running query. Based on what you've said I suspect it wouldn't tell us much here, but I wanted to mention it. 1: https://github.com/StarfishStorage/explain-running-query -- Jim Nasby, Data Architect, Austin TX