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: 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: 2023-11-21T06:18:19Z
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

Attachments

On Tue, Nov 21, 2023 at 1:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> * Do we really need to use make_tlist_from_pathtarget?  Why isn't
> the tlist of the cteplan good enough (indeed, more so)?


I think you are right.  The cteplan->targetlist is built for the CTE's
best path by build_path_tlist(), which is almost the same as
make_tlist_from_pathtarget() except that it also replaces nestloop
params.  So cteplan->targetlist is good enough here.


> * I don't love having this code assume that it knows how to find
> the Path the cteplan was made from.  It'd be better to make
> SS_process_ctes save that somewhere, maybe in a list paralleling
> root->cte_plan_ids.


Fair point.

I've updated the patch to v2 for the changes.


> Alternatively: maybe it's time to do what the comments in
> SS_process_ctes vaguely speculate about, and just save the Path
> at that point, with construction of the plan left for createplan()?
> That might be a lot of refactoring for not much gain, so not sure.


I'm not sure if this is worth the effort.  And it seems that we have the
same situation with SubLinks where we construct the plan in subselect.c
rather than createplan.c.

Thanks
Richard