On 01/05/2012 06:18 PM, Tom Lane wrote:
>
>> ERROR: joins can have at most 32767 columns
> It's the sum of the number of columns in the base tables.
>
That makes sense. I totally misunderstood the message to be referring to
the number of joined columns rather than table columns.
>
>> I've asked this list before for advice on how to work with the
>> approximately 23,000 column American Community Survey dataset,
> Are there really 23000 populated values in each row? I hesitate to
> suggest an EAV approach, but it kinda seems like you need to go in that
> direction. You're never going to get decent performance out of a schema
> that requires 100-way joins, even if you avoid bumping up against hard
> limits.
Many of the smaller geographies, e.g. census tracts, do in fact have
data for the vast majority of the columns. I am trying to combine it all
into one table to avoid the slowness of multiple JOINs (even though in
practice I'm never joining all the tables at once). EAV sounds correct
in terms of normalization, but isn't it usually better performance-wise
to store write-once/read-many data in a denormalized (i.e. flattened)
fashion? One of these days I'll have to try to benchmark some different
approaches, but for now planning on using array columns, with each
"sequence" (in the Census sense, not the Postgres sense) of 200+
variables in its own array rather than its own table.
--Lee
--
Lee Hachadoorian
PhD, Earth& Environmental Sciences (Geography)
Research Associate, CUNY Center for Urban Research
http://freecity.commons.gc.cuny.edu