From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | Justin Pryzby <pryzby(at)telsasoft(dot)com> |
Cc: | pgsql-performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query |
Date: | 2020-08-14 22:40:40 |
Message-ID: | CAD3a31VWnkTHqvQhfAkp6y4+DW2dxDbTtmOu7c=yf0Xp8C6A2Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, Aug 14, 2020 at 3:04 PM Justin Pryzby <pryzby(at)telsasoft(dot)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
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801
Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2020-08-15 00:24:27 | Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query |
Previous Message | Justin Pryzby | 2020-08-14 22:04:24 | Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query |