From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stefan Litsche <slitsche@biosoft.de>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Omitting relpages for toast table access not expected
In-reply-to: 
 <CA+bTu2RaUzKUqEdd6pPchW3tKvNcfbS6d7ADp8NUuuj871sK_A@mail.gmail.com>
References: 
 <CA+bTu2RaUzKUqEdd6pPchW3tKvNcfbS6d7ADp8NUuuj871sK_A@mail.gmail.com>
Comments: In-reply-to Stefan Litsche <slitsche@biosoft.de>
	message dated "Fri, 05 Jul 2024 16:41:41 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <203950.1720192055.1@sss.pgh.pa.us>
Date: Fri, 05 Jul 2024 11:07:35 -0400
Message-ID: <203951.1720192055@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/203951.1720192055%40sss.pgh.pa.us>
Precedence: bulk

Stefan Litsche <slitsche@biosoft.de> writes:
> I want to share three observations which show not expected behavior.

> [ after forcing data to be toasted ]

> Shouldn't the cost of the plan also reflect the cost of accessing
> pages in the toast table?

This is expected: the planner does not currently try to account for
the cost of fetching toasted values.  We have thought about that,
certainly.  But it would be quite difficult to do so without
introducing a lot of error into the numbers, since in most non-toy
situations it's hard to predict what fraction of the values fetched
by a particular query will be toasted or how big they will be.
Another reason for not expending sweat here is that in most scenarios
those costs would be the same for any possible query plan, so that the
effort involved in making a better estimate wouldn't end up improving
the plan.

> So after dropping the table, recreating and analyzing it, I do not
> get updated values for relpages for the corresponding toast table.
> I would expect that the analyze command also updates the statistics
> for the toast table.

This doesn't surprise me enormously.  We don't worry too much about
updating statistics for toast tables, because the planner wouldn't use
them anyway -- in particular I'm pretty sure ANALYZE doesn't examine
toast tables as such.  relpages might get updated as a by-product of
VACUUM, but only if VACUUM judges that it's worth scanning the toast
table, which it won't unless some toasted values have been deleted
since last time.

			regards, tom lane