Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED) - Mailing list pgsql-general

From Ron
Subject Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)
Date
Msg-id 70e05a73-bd23-b98e-3232-4c0019d01b56@gmail.com
Whole thread Raw
In response to huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)  (Kent Tong <kent.tong.mo@gmail.com>)
List 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.

pgsql-general by date:

Previous
From: Ron
Date:
Subject: Re: Additive backup and restore?
Next
From: Tom Lane
Date:
Subject: Re: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)