TOAST : partial detoasting : only a small part of a toasted value (for pgpointcloud)
Hi dear list,
I have a tricky question about TOASTED memory in Postgres related to the
[pgpointcloud](https://github.com/pgpointcloud/pointcloud) extension.
(using Postgres 11 if it matters)
So the pgpointcloud store sometimes very large groups of points into one
row (TOASTED), something along few kB to few MB. TOAST would be in EXTERNAL
mode (no compression).
Sometimes we only want to access a part of this data (one or several blocks
within the full data).
From what I understand, for the moment it requires to fetch all the data
from disk, then de-toast it, then select only the part of the data we are
interested in.
Yet I think it is possible to detoast only a subset of the data (and thus
fetch only some part of the data), considering that the [doc on toast](
https://www.postgresql.org/docs/11/storage-toast.html) says
" Use of EXTERNAL will make substring operations on wide text and bytea
columns faster
(at the penalty of increased storage space) because these operations are
optimized
to fetch only the required parts of the out-of-line value when it is not
compressed. "
So my question is how does it work, how easy would it be to implement for
pgpointcloud?
Many thanks for your time and help
Remi-C
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
So the pgpointcloud store sometimes very large groups of points into one
row (TOASTED), something along few kB to few MB. TOAST would be in EXTERNAL
mode (no compression).
Sometimes we only want to access a part of this data (one or several blocks
within the full data).From what I understand, for the moment it requires to fetch all the data
from disk, then de-toast it, then select only the part of the data we are
interested in.
Yet I think it is possible to detoast only a subset of the data (and thus
fetch only some part of the data), considering that the [doc on toast](
https://www.postgresql.org/docs/11/storage-toast.html) says" Use of EXTERNAL will make substring operations on wide text and bytea
columns faster
(at the penalty of increased storage space) because these operations are
optimized
to fetch only the required parts of the out-of-line value when it is not
compressed. "
So my question is how does it work, how easy would it be to implement for
pgpointcloud?
See PG_DETOAST_DATUM_SLICE and users of that macro.
regards, tom lane
wow,
it was right under my nose.
Thank you very much !
Cheers,
Remi-C
Le mar. 13 nov. 2018 à 19:00, Tom Lane <tgl@sss.pgh.pa.us> a écrit :
Show quoted text
=?UTF-8?Q?R=C3=A9mi_Cura?= <remi.cura@gmail.com> writes:
So the pgpointcloud store sometimes very large groups of points into one
row (TOASTED), something along few kB to few MB. TOAST would be inEXTERNAL
mode (no compression).
Sometimes we only want to access a part of this data (one or severalblocks
within the full data).
From what I understand, for the moment it requires to fetch all the
data
from disk, then de-toast it, then select only the part of the data we are
interested in.Yet I think it is possible to detoast only a subset of the data (and thus
fetch only some part of the data), considering that the [doc on toast](
https://www.postgresql.org/docs/11/storage-toast.html) says" Use of EXTERNAL will make substring operations on wide text and bytea
columns faster
(at the penalty of increased storage space) because these operations are
optimized
to fetch only the required parts of the out-of-line value when it is not
compressed. "
So my question is how does it work, how easy would it be to implement for
pgpointcloud?See PG_DETOAST_DATUM_SLICE and users of that macro.
regards, tom lane