Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query - Mailing list pgsql-performance
On Fri, Aug 14, 2020 at 3:04 PM Justin Pryzby <pryzby@telsasoft.com> wrote:
Due to the over-estimated rowcount, the planner believes that (more) rows will
be output (sooner) than they actually are:
-> Nested Loop Semi Join (cost=47.11..31367302.81 ROWS=611586 width=336) (actual time=47.098..97236.123 ROWS=25 loops=1)
So it thinks there's something to be saved/gained by using a plan that has a
low startup cost. But instead, it ends up running for a substantial fraction
of the total (estimated) cost.
Got it. Is there any way to address this other than re-writing the query? (Statistics? Or something else?)
As for the "explain is more expensive than the query", that could be due to
timing overhead, as mentioned here. Test with "explain (timing off)" ?
https://www.postgresql.org/docs/12/using-explain.html#USING-EXPLAIN-CAVEATS
Good call--explain with the timing off showed about the same time as the actual query.
Thanks!
Ken
--
Justin
AGENCY Software
A Free Software data system
By and for non-profits
(253) 245-3801
learn more about AGENCY or
follow the discussion.
pgsql-performance by date: