pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)
Date: Mon, 8 May 2023 08:43:38 -0500
Message-ID: <70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com> (raw)
In-Reply-To: <CAKs98dFv5pyZ-2yE+M6W0YFA5SXgB=Hv-sg9CPz1RfB4zTTTxw@mail.gmail.com>
References: <CAKs98dFv5pyZ-2yE+M6W0YFA5SXgB=Hv-sg9CPz1RfB4zTTTxw@mail.gmail.com>

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.

view thread (4+ messages)  latest in thread

Message-ID: <70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com>
Permalink:  ../70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com/
Also on:    postgresql.org/message-id/70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)
  In-Reply-To: <70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com>
  References: <CAKs98dFv5pyZ-2yE+M6W0YFA5SXgB=Hv-sg9CPz1RfB4zTTTxw@mail.gmail.com> <70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox