pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@enterprisedb.com>
Cc: James Finnerty <jfinnert@amazon.com>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: postgres_fdw: using TABLESAMPLE to collect remote sample
Date: Sat, 07 Jan 2023 10:20:10 -0500
Message-ID: <3447192.1673104810@sss.pgh.pa.us> (raw)
In-Reply-To: <560c80fc-823e-418d-280e-e0c7c0dad59b@enterprisedb.com>
References: <151e835f-55d6-ddbc-b5b4-07ee606aba44@enterprisedb.com>
	<03182011-10a4-97b4-7c70-d99aa1c11eed@enterprisedb.com>
	<167112207543.1120.12195641719862911275.pgcf@coridan.postgresql.org>
	<3377008.1671122816@sss.pgh.pa.us>
	<cd3f2fda-d4cc-4371-210f-fc339d53b885@enterprisedb.com>
	<6da357b0-fdd3-095d-a06c-87666f4a5a8c@enterprisedb.com>
	<951485.1672461744@sss.pgh.pa.us>
	<233ee0b3-4da0-3370-cd0b-1b8528851ddd@enterprisedb.com>
	<1040922.1672507431@sss.pgh.pa.us>
	<d13d28b9-30d0-27e8-d58e-d3e0c76b3bda@enterprisedb.com>
	<3d719e62-64d2-5184-eebf-6128bcb398e2@enterprisedb.com>
	<2813656.1672952711@sss.pgh.pa.us>
	<3e8cca01-f102-d4b9-abe9-ed7b5e938323@enterprisedb.com>
	<2837327.1672955815@sss.pgh.pa.us>
	<4ee816f5-6146-7745-a6b4-f38a1b24105a@enterprisedb.com>
	<3013665.1673024332@sss.pgh.pa.us>
	<4daee204-341f-d413-fa93-29f603f61da8@enterprisedb.com>
	<560c80fc-823e-418d-280e-e0c7c0dad59b@enterprisedb.com>

Tomas Vondra <tomas.vondra@enterprisedb.com> writes:
> However, maybe views are not the best / most common example to think
> about. I'd imagine it's much more common to reference a regular table,
> but the table gets truncated / populated quickly, and/or the autovacuum
> workers are busy so it takes time to update reltuples. But in this case
> it's also quite simple to fix the correlation by just ordering by ctid
> (which I guess we might do based on the relkind).

> There's a minor issue with partitioned tables, with foreign partitions
> pointing to remote views. This is kinda broken, because the sample size
> for individual relations is determined based on relpages. But that's 0
> for views, so these partitions get ignored when building the sample. But
> that's a pre-existing issue.

I wonder if we should stop consulting reltuples directly at all,
and instead do

"EXPLAIN SELECT * FROM remote_table"

to get the remote planner's estimate of the number of rows involved.
Even for a plain table, that's likely to be a better number.

			regards, tom lane





view thread (35+ messages)

Message-ID: <3447192.1673104810@sss.pgh.pa.us>
Permalink:  ../3447192.1673104810@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/3447192.1673104810@sss.pgh.pa.us

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-hackers@postgresql.org
  Cc: tgl@sss.pgh.pa.us, tomas.vondra@enterprisedb.com, jfinnert@amazon.com, pgsql-hackers@lists.postgresql.org
  Subject: Re: postgres_fdw: using TABLESAMPLE to collect remote sample
  In-Reply-To: <3447192.1673104810@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox