From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED) |
Date: | 2023-05-08 13:43:38 |
Message-ID: | 70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2023-05-08 13:44:18 | Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED) |
Previous Message | Ron | 2023-05-08 13:36:28 | Re: Additive backup and restore? |