From: Tom Lane <tgl@sss.pgh.pa.us>
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
In-reply-to: <64CC9527-C642-4AED-844B-70E7BC996A1B@dgitsystems.com>
References: <64CC9527-C642-4AED-844B-70E7BC996A1B@dgitsystems.com>
Comments: In-reply-to David Wheeler <dwheeler@dgitsystems.com>
	message dated "Wed, 27 Jun 2018 03:45:26 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <24442.1530124053.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 27 Jun 2018 14:27:33 -0400
Message-ID: <24443.1530124053@sss.pgh.pa.us>
Precedence: bulk

David Wheeler <dwheeler@dgitsystems.com> writes:
> 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 fr=
om a using queue where a.id<http://a.id> =3D queue.id<http://queue.id>), b=
ut postgres stats say that queue table is empty, so it uses a nested loop =
over all 30 million rows, taking forever

Although there's no way to have any useful pg_statistic stats if you won't
do an ANALYZE, the planner nonetheless can see the table's current
physical size, and what it normally does is to multiply the last-reported
tuple density (reltuples/relpages) by the current size.  So if you're
getting an "empty table" estimate anyway, I have to suppose that the
table's state involves reltuples =3D 0 and relpages > 0.  That's not a
good place to be in; it constrains the planner to believe that the table
is in fact devoid of tuples, because that's what the last ANALYZE saw.

Now, the initial state for a freshly-created or freshly-truncated table
is *not* that.  It is reltuples =3D 0 and relpages =3D 0, representing an
undefined tuple density.  Given that, the planner will make some guess
about average tuple size --- which is likely to be a very good guess,
for a table with only fixed-width columns --- and then compute a rowcount
estimate using that plus the observed physical size.

So I think your problem comes from oscillating between really-empty
and not-at-all-empty, and not using an idiomatic way of going back
to the empty state.  Have you tried using TRUNCATE instead of DELETE?

> This queue table is empty 99% of the time, and the query in 3 runs immed=
iately after step 2. Is there any likelyhood that tweaking the autoanalyze=
 params would help in this case? I don't want to explicitly analyze the ta=
ble between steps 2 and three either as there are other patterns of use wh=
ere for example 0 rows are inserted in step 2 and this is expected to run =
very very quickly. Do I have any other options?

I am not following your aversion to sticking an ANALYZE in there,
either.  It's not like inserting 30 million rows would be free.

			regards, tom lane