Thread: huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED)

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?

Kent Tong <kent.tong.mo@gmail.com> writes:
> I have a complex query involving over 15 joins and a CTE query and it takes
> over 17s to complete.

If you want constructive comments about that, you're going to need to show
us the whole thing (and the underlying tables' schemas).  Little bits in
isolation are not very intelligible.

https://wiki.postgresql.org/wiki/Slow_Query_Questions

One thought is that you might need to raise from_collapse_limit and/or
join_collapse_limit to at least 15 to get the planner to do a complete
search of the join-order space.

            regards, tom lane