Обсуждение: Are unreferenced TOASTed values retrieved?

Hi guys,

Imagine if you will that I have a table thus

CREATE TABLE "lumps" (
    "id"    SERIAL PRIMARY KEY,
    "name"    TEXT NOT NULL,
    "data"    BYTEA NOT NULL
);

Imagine I have stored say 1000 rows.

In each row, we have stored on average
20 bytes in column "name",
10 megabytes in column "data".

So my table contains 10 gigabytes of "data" and 20 kilobytes of "name"s.

The values in colum "data" will presumably be TOASTed.

Now, I go ahead and run the following query:

SELECT "name" FROM "lumps";

Clearly the query will need to retrieve something from all 1000 rows.

And now we get to the question:

Will the query engine retrieve the entire row (including 10 megabytes of
out-of-line TOASTed data) for every row, and then pick out column
"name", and take an age to do so, OR will the query engine retrive just
the "direct" row, which includes "name" in-line, and return those to me,
in the blink of an eye?

Clearly the former would be slow and undesirable, and the latter quick
and desirable.

Regards,

Bill

William Blunn <bill+postgresql@blunn.org> writes:
> Will the query engine retrieve the entire row (including 10 megabytes of
> out-of-line TOASTed data) for every row, and then pick out column
> "name", and take an age to do so,

No.  That's pretty much the whole point of the TOAST mechanism;
out-of-line values are not fetched unless actually needed.  See
http://developer.postgresql.org/pgdocs/postgres/storage-toast.html

            regards, tom lane