Skip site navigation (1) Skip section navigation (2)

Are unreferenced TOASTed values retrieved?

From: William Blunn <bill+postgresql(at)blunn(dot)org>
To: pgsql-performance(at)postgresql(dot)org
Subject: Are unreferenced TOASTed values retrieved?
Date: 2009-10-21 17:26:16
Message-ID: 4ADF4438.3000702@blunn.org (view raw or flat)
Thread:
Lists: pgsql-performance
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

Responses

pgsql-performance by date

Next:From: phb07@apra.asso.frDate: 2009-10-21 17:55:18
Subject: Re: maintain_cluster_order_v5.patch
Previous:From: Scott CareyDate: 2009-10-21 17:17:47
Subject: Re: There is a statistic table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group