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

Tom Lane <tgl@sss.pgh.pa.us>

From: Tom Lane <tgl@sss.pgh.pa.us>

To: Alexander Lakhin <exclusion@gmail.com>

Cc: Richard Guo <guofenglinux@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-06T22:41:14Z

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 →

Allow examine_simple_variable() to work on INSERT RETURNING Vars.
- 89b69db82adf 17.0 landed
Extract column statistics from CTE references, if possible.
- f7816aec23ee 17.0 landed
Remove SQL regression tests for GUCs related to NO_SHOW_ALL
- dbe8a1726cfd 15.3 cited

Attachments

fix-examine-variable-for-INSERT-RETURNING.patch (text/x-diff) patch

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.

Thanks for the report!  I guess we need something like the attached.
I'm surprised that this hasn't been noticed before; was the case
really unreachable before?

			regards, tom lane