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.
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