Re: Query Optimisation and TEXT fields

Andrew McMillan <Andrew(at)catalyst(dot)net(dot)nz> writes:
> I am having some problems getting optimised queries when I use TEXT
> fields in records. It seems that PostgreSQL is assuming that these
> fields are 4 bytes wide so the record width calculation is wrong and
> this means that all of the dependant calculations are wrong.

4 bytes? I'd have expected 12 (see _DEFAULT_ATTRIBUTE_WIDTH_ as used
in src/backend/optimizer/path/costsize.c). While this is obviously
pretty brain-dead, I have not seen many cases in which that particular
bogosity was the limiting factor in the accuracy of the optimizer's
calculations. Usually it's the row count rather than row width that
we're hopelessly lost on :-(

At some point it might be useful for VACUUM to calculate a real
average-field-width value for varlena columns and store same in
pg_statistic. I can't get excited about it quite yet though.
If you dig into costsize.c you'll see that the estimated row width
is just a minor factor in the estimates. In particular, it has no
relevance whatever for seqscan-vs-indexscan choices.

regards, tom lane