From: | Kent Tong <kent(dot)tong(dot)mo(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | huge discrepancy between EXPLAIN cost and actual time (but the table has just been ANALYZED) |
Date: | 2023-05-08 12:29:09 |
Message-ID: | CAKs98dFv5pyZ-2yE+M6W0YFA5SXgB=Hv-sg9CPz1RfB4zTTTxw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Many thanks in advance
--
Kent Tong
IT author and consultant, child education coach
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2023-05-08 13:36:28 | Re: Additive backup and restore? |
Previous Message | Andreas Kretschmer | 2023-05-08 11:02:42 | Re: Additive backup and restore? |