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: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
Hi guys,

Imagine if you will that I have a table thus

CREATE TABLE "lumps" (
    "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.




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-2017 The PostgreSQL Global Development Group