Re: BUG #15455: Endless lseek

On 2018-10-24 11:14:29 +0530, Dilip Kumar wrote:
> On Tue, Oct 23, 2018 at 9:35 PM PG Bug reporting form
> <noreply(at)postgresql(dot)org> wrote:
> >
> > The following bug has been logged on the website:
> >
> > Bug reference: 15455
> > Logged by: Andrea Ferranti
> > Email address: andrea(dot)ferranti(at)wolterskluwer(dot)com
> > PostgreSQL version: 9.6.10
> > Operating system: Ubuntu 16
> > Description:
> >
> I think you have already raised the same bug "BUG #15454: Endless
> lseek", Seems this is duplicate.

I think the other bug is incomplete / truncated...

> > We have experienced a strange behaviour on PostgreSQL 9.6.10.
> > (for privacy reason I cannot copy the exact query and data on which query
> > are performed).
> >
> > We have a series of query that move data from a table to another by using
> > the following logic.
> >
> > create table A;
> > insert data into A from B;
> > <insert remaining data into A from B>;
> > drop B;
> > rename A to B;
> >
> > the <insert remaining data into A from B> step has the following logic:
> >
> > INSERT INTO A SELECT X
> > FROM B DEST_TABLE JOIN (
> > SELECT ---
> > FROM B SOURCE_TABLE)
> > SOURCE_TABLE
> > ON SOURCE_TABLE.S0 = DEST_TABLE.
> > AND SOURCE_TABLE.S1 = DEST_TABLE.
> >
> > the last query doesn't terminate.
> > In particular, we have found that the inner select (...FROM A JOIN B...)
> > generate a series of lseek as following.
> >
> > (we have execute a strace of posgtreSQL process)

> > by adding a "sleep" of 30 seconds before the <insert remaining data into A
> > from B>; everything works and following is the strace of the correct
> > process:

I assume this might "just" be a chance for autovacuum to analyze the
table. If you do an EXPLAIN of the query both with the 30s wait and
without, does the plan change? Does adding an explicit ANALYZE of both
a and b before inserting fix the issue?

Greetings,

Andres Freund