Date: Fri, 14 Aug 2020 17:04:24 -0500
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
Message-ID: <20200814220424.GK4561@telsasoft.com>
References: 
 <CAD3a31UJVhu-Ojsikjz12PEdvbz_ha_4U-BzPn5smKUyrECO6A@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CAD3a31UJVhu-Ojsikjz12PEdvbz_ha_4U-BzPn5smKUyrECO6A@mail.gmail.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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