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 1u0Gaj-00FTEh-31 for pgsql-performance@arkaria.postgresql.org; Thu, 03 Apr 2025 09:10:29 +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 1u0Gah-0054bw-QY for pgsql-performance@arkaria.postgresql.org; Thu, 03 Apr 2025 09:10:27 +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 1u0Gah-0054bo-Ar for pgsql-performance@lists.postgresql.org; Thu, 03 Apr 2025 09:10:27 +0000 Received: from mail-ej1-x632.google.com ([2a00:1450:4864:20::632]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1u0Gaf-002hwC-1f for pgsql-performance@lists.postgresql.org; Thu, 03 Apr 2025 09:10:26 +0000 Received: by mail-ej1-x632.google.com with SMTP id a640c23a62f3a-ac3b12e8518so128714966b.0 for ; Thu, 03 Apr 2025 02:10:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgmustard.com; s=google; t=1743671424; x=1744276224; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=JKo3bMk2IwHmZ4RZRAYBV3RDpw7ANFHuNlIk92ZzCH0=; b=B0cWwxlqxU7EOg8HHxq7w7e/2khdhpCtH2Q9Agvgvp9/mPsCn9HA87oL+/TtWrV+Kg k4UzF8XsYjC4+nuQHfDy8oDloaahI10vLqMEuG1UphIO+6GWwoJQjmAhNEK03zMoesSb KBBf3kHY6S93VS4hrs5E7Vbf/MR0zLyI5XdqE2JVXoDXZufISBzYdzfJmwqE+QwC4sSA bT6BzW1dr3Knf+mPl3prt0bVOouK0Lo+LAk3HAEsEYgUMJBs5u6WVqGWbfwVnf4sYUx1 L3Wmb4GEJsfEJ5hHr8yAmhrMcl1IkhwhttXh8ZPNqrtDyjLPJPt00VkcceBgfcVtKjvD PERg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1743671424; x=1744276224; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=JKo3bMk2IwHmZ4RZRAYBV3RDpw7ANFHuNlIk92ZzCH0=; b=IzjobWBlVVvSeHO+1/+gdNgnS55dgijEEQZa6Yn7eZjty8ycM1iz/bnCNwM1Y711eu l8qcrhuy614j1lJMnYOE/CxYHG3cmfzIAozytaIcoxiEFDxtn7xErjo04p3XS+kSC3YO IRM0nmcsfuoD+X69XUQStHLTAwIYxInFMNQD5BU5dSDwxSEOI2zv4V89vNnVhGmNV7CV eMVajSokEhAeaiwAIF10kuh3HtlmXDm91yWGqZcAa2sIRGXjNqfkOQvyb63NPHsg/v7K 1F8LPUuW47/bVvUgg2f1s/YGwkV7MnmdTycvGsg4A15UBpyRzIziZXb+00WPouN0bTI8 DiDw== X-Gm-Message-State: AOJu0YyJB9F4ufF3tcbHfkbv3dCseMrKJlmsCu4cnwycidmMazk2s4zd ZLSKeFXNMOGACOTu6P6crWm/yj0xl/RkQ+8gZ3qCkD2RcVLXT57AP6vYloh+tkg5dOsC5MiyNNu 9aF9XJHPePWvpkT725r2Nhx1AHWEiIxwiWtvzFw== X-Gm-Gg: ASbGncuugsqLDQ1cyQwF0bbDC8vKIPkwqsbvqEE4bcefCCslu3/dDGeiT5jPV2DJt82 rgoyxE7cm2r8Yj5HepBwK6Bbk90dcwycYEg6RppGuF7eL2PK5PZrofYmFgh80RE3oWdPZeeNgXi wJj+H3/auT7b5SBqX9FS2Jv+osbxY= X-Google-Smtp-Source: AGHT+IGeLxZn7JEV5tc64OQbohLVU2JLJXRjzRtsJJWPSD/zljfvJJqyBhhgbgxph7qiZmDsf9/9jCrL5uPnXJqekLQ= X-Received: by 2002:a17:907:9384:b0:ac7:c79d:f8ce with SMTP id a640c23a62f3a-ac7c79dfa73mr33393266b.57.1743671423653; Thu, 03 Apr 2025 02:10:23 -0700 (PDT) MIME-Version: 1.0 References: <87msczsl7y.fsf@gmail.com> <87h636ssiq.fsf@gmail.com> In-Reply-To: From: Michael Christofides Date: Thu, 3 Apr 2025 10:10:07 +0100 X-Gm-Features: ATxdqUEik6LNpHeG2Ls0ktSO_mpJoWnOMSykK7yk-oPBv2_yQ0GqvslbHJwKfpQ Message-ID: Subject: Re: Very slow query performance when using CTE To: Chris Joysn Cc: pgsql-performance@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000cff6270631dc230f" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000cff6270631dc230f Content-Type: text/plain; charset="UTF-8" > > CREATE STATISTICS st_simrun_component_metadata (dependencies) ON > sim_run_id, key FROM sim_run_component_metadata; > ANALYZE sim_run_component_metadata; > > When I run this query, no statistics are returned: > > SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = > stxoid), > > pg_mcv_list_items(stxdmcv) m WHERE stxname = > 'st_simrun_component_metadata'; > > Is there something I might have missed? > It looks like you created "dependencies" statistics, but then searched for "mcv" statistics. To test if mcv helps, you could drop and recreate as: CREATE STATISTICS st_simrun_component_metadata (mcv) ... The fetch from the table is rather fast. some milliseconds. But a > subsequent sort operations takes very long time, for the amount of records > fetched. > This does not seem to be the case for the slow cases you shared (those are dominated by several millisecond index scans that are looped over 32k times). So I assume you're talking about the fast case? If so, there is a Sort that takes a couple of hundred milliseconds being done on disk (~15MB) so you might also want to look into how fast that would be in memory (via work_mem). > But, just like the estimated rows in the plan, it does not match the real > amount of available data in the table: > I'm not sure what you mean by this, is it only that the row estimates are still bad? Regards, Michael --000000000000cff6270631dc230f Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
CREATE STATISTICS st_simrun_component_metadata (dependencies) ON sim_run_id, key FROM sim_run_component_metadata;
ANALYZE= sim_run_component_metadata;

When I run this query, no statistics are returned:

SELECT m.= * FROM pg_statistic= _ext join= pg= _statistic_ext_data on (oid =3D stxoid),

pg_mcv_list_items(stxdmcv) m WHERE stxname =3D 'st_simrun_component_met= adata';


Is there something I might have missed?

It looks like you created &= quot;dependencies" statistics, but then searched for "mcv" s= tatistics. To test if mcv helps, you could drop and recreate as: CREATE STA= TISTICS st_simrun_component_metadata (mcv) ...=C2=A0

The fetch from the table is rather fast. so= me milliseconds. But a subsequent sort operations takes very long time, for= the amount of records fetched.
This does not seem to be the case for the slow cases you shared (those ar= e dominated by several millisecond index scans that are looped over 32k tim= es). So I assume you're talking about the fast case? If so, there is a = Sort that takes a couple of hundred milliseconds being done on disk (~15MB)= so you might also want to look into how fast that would be in memory (via = work_mem).
=C2=A0
<= /div>
But, just like the estimated rows in the plan, it does not match = the real amount of available data in the table:

I'm not sure what you mean by this, is it = only that the row estimates are still bad?

Regards= ,
Michael
--000000000000cff6270631dc230f--