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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])