From: Justin Pryzby <pryzby@telsasoft.com>
To: Ken Tanzer <ken.tanzer@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query
Date: Fri, 14 Aug 2020 17:04:24 -0500
Message-ID: <20200814220424.GK4561@telsasoft.com> (raw)
In-Reply-To: <CAD3a31UJVhu-Ojsikjz12PEdvbz_ha_4U-BzPn5smKUyrECO6A@mail.gmail.com>
References: <CAD3a31UJVhu-Ojsikjz12PEdvbz_ha_4U-BzPn5smKUyrECO6A@mail.gmail.com>
On Fri, Aug 14, 2020 at 02:34:52PM -0700, Ken Tanzer wrote:
> Hi. I've got a query that runs fine (~50ms). When I add a "LIMIT 25" to
> it though, it takes way longer. The query itself then takes about 4.5
> seconds. And when I do an explain, it takes 90+ seconds for the same query!
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.
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
--
Justin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, ken.tanzer@gmail.com
Subject: Re: Query takes way longer with LIMIT, and EXPLAIN takes way longer than actual query
In-Reply-To: <20200814220424.GK4561@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox