From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?UTF-8?Q?Strahinja_Kustudi=C4=87?= <strahinjak@nordeus.com>
cc: Pavel Stehule <pavel.stehule@gmail.com>, Peter Geoghegan <pg@bowt.ie>,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17330: EXPLAIN hangs and very long query plans
In-reply-to: 
 <CADKbJJVUfzb6BGUfH-zmvSJArfhPUFUGpM+ctHquQjF5WzeTTA@mail.gmail.com>
References: <17330-0d6d4cf6ff440a65@postgresql.org>
 <CAH2-WznTMcQF7jjQg2g83o5uwLEj8tzOwsvSo1foE_LHbGqN3A@mail.gmail.com>
 <CADKbJJUziW53FCNzMZSfDompyQ8aWF1ZJjjhy8OHPWJ0smWR5Q@mail.gmail.com>
 <CAFj8pRD9Pv=aWzGMXy2P2XurM31mzdKpub+vJ0SwTJu1F3SJ-w@mail.gmail.com>
 <CADKbJJVUfzb6BGUfH-zmvSJArfhPUFUGpM+ctHquQjF5WzeTTA@mail.gmail.com>
Comments: In-reply-to =?UTF-8?Q?Strahinja_Kustudi=C4=87?=
 <strahinjak@nordeus.com>
	message dated "Fri, 10 Dec 2021 16:52:45 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <70743.1639154362.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Fri, 10 Dec 2021 11:39:22 -0500
Message-ID: <70745.1639154362@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/70745.1639154362%40sss.pgh.pa.us>
Precedence: bulk

=?UTF-8?Q?Strahinja_Kustudi=C4=87?= <strahinjak@nordeus.com> writes:
> On Fri, Dec 10, 2021 at 10:53 AM Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>> pá 10. 12. 2021 v 10:25 odesílatel Strahinja Kustudić <
>> strahinjak@nordeus.com> napsal:
>>> I allowed EXPLAIN in Docker to finish and it took almost 500 seconds and
>>> it was reading data all the time. After I reindexed the biggest table,
>>> EXPLAIN finished instantly. Can the index corruption cause this?

>> The index can be bloated.

> Is bloat just enough to make it slower, or the index needs to be corrupted?

bloat != corruption.  The problem you're dealing with here is a whole lot
of dead index entries at the very end of the index range (could be either
the lowest or highest entries).  The planner is wasting time scanning
through those to find the extremal non-dead value for a range estimate.
REINDEX got rid of the dead entries; a VACUUM would have done as well.

As noted upthread, more recent PG versions are a bit smarter about
such cases.

			regards, tom lane