Date: Sun, 24 Feb 2019 14:20:37 -0600
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.
Message-ID: <20190224202037.GK28750@telsasoft.com>
References: <33bae6e3-1c5a-77f0-ee99-408fbf0fbb83@gusw.net>
 <CAKJS1f_kxF=3K=D8MXa2NwdycwKJsNqVXNXyeDWv3t9f3p6J2A@mail.gmail.com>
 <c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <c044fc83-4816-e846-e78c-b4d6961efb6f@gusw.net>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk

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