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
Date: Fri, 05 Jul 2024 11:07:35 -0400
Message-ID: <203951.1720192055@sss.pgh.pa.us> (raw)
In-Reply-To: <CA+bTu2RaUzKUqEdd6pPchW3tKvNcfbS6d7ADp8NUuuj871sK_A@mail.gmail.com>
References: <CA+bTu2RaUzKUqEdd6pPchW3tKvNcfbS6d7ADp8NUuuj871sK_A@mail.gmail.com>
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
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-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, slitsche@biosoft.de, pgsql-bugs@lists.postgresql.org
Subject: Re: Omitting relpages for toast table access not expected
In-Reply-To: <203951.1720192055@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