From: Tom Lane <tgl@sss.pgh.pa.us>
To: Strahinja Kustudić <strahinjak@nordeus.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Peter Geoghegan <pg@bowt.ie>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17330: EXPLAIN hangs and very long query plans
Date: Fri, 10 Dec 2021 11:39:22 -0500
Message-ID: <70745.1639154362@sss.pgh.pa.us> (raw)
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>
=?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
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, strahinjak@nordeus.com, pavel.stehule@gmail.com, pg@bowt.ie, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17330: EXPLAIN hangs and very long query plans
In-Reply-To: <70745.1639154362@sss.pgh.pa.us>
* 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