Re: TODO Item - Return compressed length of TOAST datatypes

Neil Conway wrote:
> Mark Kirkwood wrote:
>
>> I didn't performance test it, but the idea of hammering the catalogs for
>> each value to be processed seemed a bad thing.
>
>
> Well, the syscache already sits in front of the catalogs themselves. I'd
> be curious to see what the performance difference actually is...
>
>

I did some tests with a two tables, one small and one large, I am seeing
a consistent difference favoring the first-call-only type checking:

Table "public.dim1"
Column | Type | Modifiers
--------+------------------------+-----------
d1key | integer | not null
dat | character varying(100) | not null
dattyp | character varying(20) | not null
dfill | character varying(100) | not null

INFO: "dim1": scanned 24 of 24 pages, containing 1001 live rows and 0
dead rows; 1001 rows in sample, 1001 estimated total rows

SELECT max(pg_column_size(dfill)) FROM dim1

Run 1st call only check? elapsed(ms)
1 y 5.5
2 y 3.1
3 n 11.3
4 n 4.1

Now try a bigger table:

Table "public.fact0"
Column | Type | Modifiers
--------+------------------------+-----------
d0key | integer | not null
d1key | integer | not null
d2key | integer | not null
fval | integer | not null
ffill | character varying(100) | not null

INFO: "fact0": scanned 3000 of 18182 pages, containing 165000 live rows
and 0 dead rows; 3000 rows in sample, 1000010 estimated total rows

SELECT max(pg_column_size(ffill)) FROM fact0

Run 1st call only check? elapsed(ms)
1 y 2497
2 y 2484
3 y 2496
4 y 2480
5 n 3572
6 n 3570
7 n 3558
8 n 3457