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 1pw19W-0004gD-HJ for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 13:43:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pw19V-0008Sq-3y for pgsql-general@arkaria.postgresql.org; Mon, 08 May 2023 13:43:45 +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 1pw19U-0008Sh-M2 for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 13:43:44 +0000 Received: from mail-yb1-xb30.google.com ([2607:f8b0:4864:20::b30]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pw19Q-001D6r-Px for pgsql-general@lists.postgresql.org; Mon, 08 May 2023 13:43:43 +0000 Received: by mail-yb1-xb30.google.com with SMTP id 3f1490d57ef6-b9dea9d0360so6209918276.1 for ; Mon, 08 May 2023 06:43:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1683553419; x=1686145419; 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=A7ccevcaZK4B6XiP8UiP6208sqisoLqtoazyATBPYrE=; b=H6o1vz5xrYBaCUrFhkaP4JSmWyyTfVM47ID+jKm478DUeJyQfQA6IwdHRmr0Yqs2b+ mHshbljhn7ZT5492lgOyf5DeG8gdxIMgZaUiskaHEfbBUnqtbJQij4VLZdYcXf9dXoGb gUH4Zr7Xv9JhnVFcEHeE7cZWDA8Xu5priQgTQJGrh5hsDhzLUgo6TqubHRNJ5rJf1EpG D44RMF2hkHhRDOWlCHgR3QWoYk0hYKmmXo9cZyNr8o4oDNXcwTp5XZJUmW5j4EytnrKt tMo2sf6ethW+OSuTNRgSofdf/TXkAEYfn1A4nb9dz8GpSYZ9rbYHYlTe/WuQpL6yvuQv l0kA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1683553419; x=1686145419; 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=A7ccevcaZK4B6XiP8UiP6208sqisoLqtoazyATBPYrE=; b=dIiYl+7ams4EGNyRwHqiLKSxRY8nbnlPWaoaM+J1VDJHB3d/tokvoEQVq38arEtobv Gz4dKBSshP4xXKM1jZjV6U2x02J/1qH6/426cL30NF/TZ+XGoJBwzs7oaXyb4D3ICd5O zRZYiIexk6g/Woo/ZvaRYg5g0ak8q6KcVsEBNyVDv27kGvuDg/GDdnsoP9kw0vedKBzO fFIxfSPp3Tci0nT02LgDtVfiZSi76fc2SO6acc2JRV2xh+OzDECVFf6mQ/dTdBbqDOm4 HSDC72JN9UW3VOF2EcvoWL2GY89XfJiExyKIlDRCM/5cI32513z9FILLI0ukEmCWtxy5 Hm4Q== X-Gm-Message-State: AC+VfDznmrO9nl4ATTbSKPEL7YwPbSvsN3FUiobhyEyUN4WChbTKcZvw qizwZUWHvvwWRl3e3g/3mzbW3Ef5GBA= X-Google-Smtp-Source: ACHHUZ7Vj2J3CKEmG+u5gHLT3p3+cVRotkgRllbSvna3A+Fq3ku5AtO/mUwEPJRZXXikL54ApcMsjA== X-Received: by 2002:a25:dc43:0:b0:b9e:6d83:7bcf with SMTP id y64-20020a25dc43000000b00b9e6d837bcfmr10322514ybe.27.1683553419395; Mon, 08 May 2023 06:43:39 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:c103:a15d:76e:31fc? ([2600:1700:bd2c:b810:c103:a15d:76e:31fc]) by smtp.googlemail.com with ESMTPSA id b8-20020a253408000000b00ba2526a870esm1343681yba.43.2023.05.08.06.43.39 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 08 May 2023 06:43:39 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------lor3lDPCgsRSrhJuxuVX1xw0" Message-ID: <70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com> Date: Mon, 8 May 2023 08:43:38 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.10.0 Subject: Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED) Content-Language: en-US To: pgsql-general@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. --------------lor3lDPCgsRSrhJuxuVX1xw0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 5/8/23 07:29, Kent Tong wrote: > Hi, > > I have a complex query involving over 15 joins and a CTE query and it > takes over 17s to complete. The output of EXPLAIN ANALYZE includes > (somewhere deep inside): > > Index Scan using document_pkey on document document0_ >  (cost=0.29..8.31 rows=1 width=3027) (actual time=16243.959..16243.961 > rows=1 loops=1) > > > This shows an index scan with a very small cost but a very large actual > time. The strange thing is, all the tables have just been analyzed with > the ANALYZE command (it is not a foreign table). Furthermore, if I run a > simple query using that index, both the cost and the actual time are small. > > Another snippet is: > >                                   -> CTE Scan on all_related_document > p  (cost=1815513.32..3030511.77 rows=241785 width=16) (actual > time=203.969..203.976 rows=0 loops=1) > > > I think the cost-actual time discrepancy is fine as it is a recursive CTE > so postgresql can't estimate the cost well. It is materialized and a full > table scan is performed. However, the actual time is not that bad.  Also, > the estimated rows and the actual rows are also vastly different, but I > guess this is fine, isn't it? > > Any idea how I should check further? ANALYZE just samples the table.  If data within the relevant indexed columns aren't evenly distributed, then the statistics might not show the true data distribution. -- Born in Arizona, moved to Babylonia. --------------lor3lDPCgsRSrhJuxuVX1xw0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 5/8/23 07:29, Kent Tong wrote:
Hi,

I have a complex query involving over 15 joins and a CTE query and it takes over 17s to complete. The output of EXPLAIN ANALYZE includes (somewhere deep inside):

Index Scan using document_pkey on document document0_  (cost=0.29..8.31 rows=1 width=3027) (actual time=16243.959..16243.961 rows=1 loops=1)

This shows an index scan with a very small cost but a very large actual time. The strange thing is, all the tables have just been analyzed with the ANALYZE command (it is not a foreign table). Furthermore, if I run a simple query using that index, both the cost and the actual time are small.

Another snippet is:
                                                                                                                                                        -> CTE Scan on all_related_document p  (cost=1815513.32..3030511.77 rows=241785 width=16) (actual time=203.969..203.976 rows=0 loops=1) 

I think the cost-actual time discrepancy is fine as it is a recursive CTE so postgresql can't estimate the cost well. It is materialized and a full table scan is performed. However, the actual time is not that bad.  Also, the estimated rows and the actual rows are also vastly different, but I guess this is fine, isn't it? 

Any idea how I should check further?

ANALYZE just samples the table.  If data within the relevant indexed columns aren't evenly distributed, then the statistics might not show the true data distribution.

--
Born in Arizona, moved to Babylonia.
--------------lor3lDPCgsRSrhJuxuVX1xw0--