Horton

Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500

Richard Guo <guofenglinux@gmail.com>

View thread
From: Richard Guo <guofenglinux@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Alexander Lakhin <exclusion@gmail.com>, Jian Guo <gjian@vmware.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Hans Buschmann <buschmann@nidsa.net>, "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>, Zhenghua Lyu <zlyu@vmware.com>
Date: 2024-01-08T11:14:11Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow examine_simple_variable() to work on INSERT RETURNING Vars.

  2. Extract column statistics from CTE references, if possible.

  3. Remove SQL regression tests for GUCs related to NO_SHOW_ALL

On Sun, Jan 7, 2024 at 6:41 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Alexander Lakhin <exclusion@gmail.com> writes:
> > Please look at the following query:
> > CREATE TABLE t(i int);
> > INSERT INTO t VALUES (1);
> > VACUUM ANALYZE t;
>
> > WITH ir AS (INSERT INTO t VALUES (2) RETURNING i)
> > SELECT * FROM ir WHERE i = 2;
>
> > which produces ERROR:  no relation entry for relid 1
> > starting from f7816aec2.


Nice catch.


> Thanks for the report!  I guess we need something like the attached.


+1.


> I'm surprised that this hasn't been noticed before; was the case
> really unreachable before?


It seems that this case is only reachable with Vars of an INSERT target
relation, and it seems that there is no other way to reference such a
Var other than using CTE.

Thanks
Richard