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