From: Justin Pryzby <pryzby@telsasoft.com>
To: Gunther <raj@gusw.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
Date: Sun, 24 Feb 2019 14:20:37 -0600
Message-ID: <20190224202037.GK28750@telsasoft.com> (raw)
In-Reply-To: <c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net>
<CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
<c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
On Sun, Feb 24, 2019 at 12:45:34PM -0500, Gunther wrote:
> What I am most puzzled by is that no matter how long I wait, the DROP INDEX
> CONCURRENTLY never completes. Why is that?
https://www.postgresql.org/docs/11/sql-dropindex.html
CONCURRENTLY
[...] With this option, the command instead waits until conflicting transactions have completed.
Do you have a longrunning txn ? That's possibly the cause of the original
issue; vacuum cannot mark tuples as dead until txns have finished.
> I consider this ultimately a bug, or at the very least there is room for
> improvement. And I am on version 11.1.
Did you try upgrading to 11.2 ? I suspect this doesn't affect the query plan..but may be relevant?
https://www.postgresql.org/docs/11/release-11-2.html
|Improve ANALYZE's handling of concurrently-updated rows (Jeff Janes, Tom Lane)
|
|Previously, rows deleted by an in-progress transaction were omitted from ANALYZE's sample, but this has been found to lead to more inconsistency than including them would do. In effect, the sample now corresponds to an MVCC snapshot as of ANALYZE's start time.
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, raj@gusw.net
Subject: Re: Massive parallel queue table causes index deterioration, but REINDEX fails with deadlocks.
In-Reply-To: <20190224202037.GK28750@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