From: Justin Pryzby <pryzby@telsasoft.com>
To: David Wheeler <dwheeler@dgitsystems.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Queue table that quickly grows causes query planner to choose poor plan
Date: Wed, 27 Jun 2018 13:05:18 -0500
Message-ID: <20180627180518.GF3554@telsasoft.com> (raw)
In-Reply-To: <64CC9527-C642-4AED-844B-70E7BC996A1B@dgitsystems.com>
References: <64CC9527-C642-4AED-844B-70E7BC996A1B@dgitsystems.com>
On Wed, Jun 27, 2018 at 03:45:26AM +0000, David Wheeler wrote:
> Hi All,
>
> I’m having performance trouble with a particular set of queries. It goes a bit like this
>
> 1) queue table is initially empty, and very narrow (1 bigint column)
> 2) we insert ~30 million rows into queue table
> 3) we do a join with queue table to delete from another table (delete from a using queue where a.id<http://a.id; = queue.id<http://queue.id;), but postgres stats say that queue table is empty, so it uses a nested loop over all 30 million rows, taking forever
If it's within a transaction, then autovacuum couldn't begin to help until it
commits. (And if it's not, then it'll be slow on its own).
It seems to me that you can't rely on autoanalyze to finish between commiting
step 2 and beginning step 3. So you're left with options like: SET
enable_nestloop=off; or manual ANALZYE (or I guess VACUUM would be adequate to
set reltuples). Maybe you can conditionalize that: if inserted>9: ANALYZE queue.
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, dwheeler@dgitsystems.com
Subject: Re: Queue table that quickly grows causes query planner to choose poor plan
In-Reply-To: <20180627180518.GF3554@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