Thread: JOIN column maximum

Lee Hachadoorian <lee.hachadoorian@gmail.com> writes:
> How is the number of columns in a join determined? When I combine somewhere
> around 90 tables in a JOIN, the query returns:

> ERROR: joins can have at most 32767 columns

It's the sum of the number of columns in the base tables.

> I'm sure most people will say "Why the hell are you joining 90 tables."

Not only that, but why are you working with over-300-column tables?
Seems like your schema design needs rethinking.

> 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.

            regards, tom lane

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

On Thu, Jan 5, 2012 at 6:10 PM, Lee Hachadoorian
<lee.hachadoorian@gmail.com> wrote:

>
> 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.

Are you using arrays or hstore?

Lee Hachadoorian wrote:
> On 01/05/2012 06:18 PM, Tom Lane wrote:
>> 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.

EAV is not necessarily more correct than what you're doing.

The most correct solution is one where your database schema defines, and the
DBMS enforces, all of the constraints or business rules on your data, so that
you can not put something in the database that violates the business rules.

Traditional EAV, if you're talking about the common binary table of
unconstrained field-name,field-value pairs, is not an improvement.

A more correct solution is to use different columns for things with different
business rules or data types.  If the DBMS can't handle this then that is
grounds for improving the DBMS.

There's no reason that joins *have* to be slow, and in some DBMS designs you can
join in linear time, its all about how you implement.

This all being said, 23K values per row just sounds wrong, and I can't imagine
any census forms having that many details.

Do you, by chance, have multiple values of the same type that are in different
fields, eg telephone_1, telephone_2 or child_1, child_2 etc?  You should take
any of those and collect them into array-typed fields, or separate tables with
just telephone or child columns.  Or do you say have a set of coordinates in
separate fields?  Or you may have other kinds of redundancy within single rows
that are best normalized into separate rows.

With 23K values, these probably have many mutual associations, and you could
split that table into a bunch of other ones where columns that relate more
closely together are collected.

What I said in the last couple paragraphs is probably your earliest best thing
to fix, so you both have a better design and it performs together on the DBMS
you have.

-- Darren Duncan

On 6 Jan 2012, at 22:56, Lee Hachadoorian wrote:

> What I am working with is called the summary file, which presents the data in aggregate. That means you have an
incometable with 16 income classes, plus a total column. 

That could be a table: income class and income.

It may make sense to add a record for a "virtual" class 'TOTAL' there, that incorporates the totals column, although I
expectthose same numbers could be obtained by adding up the numbers in the other categories. If those numbers are
roundedthough, then your totals can be off a bit that way (due to lack of precision). 

> Then you have 9 more tables which show the same income classes by 9 racial and ethnic categories, for a total of 153
morecolumns. 

That could be a table: income class (FK), ethnic category and income.

You could turn these into 9 separate views with one ethnic category pre-selected in each.

> Then you also have a table which crosses 9 income classes by 5 mobility statuses (living in same house, moved within
county,moved within state, etc.) for a total of 55 columns. 

Another table: income class (FK), mobility status, income.

> Then you have income classes crossed with sex, income classes crossed with mode of transportation to work, sex
crossedwith mode of transportation to work, etc. 

...etc.

> When all is said and done, you have 23,000 columns.

You can definitely normalise that data much more than you are doing. I also think you may be underestimating your
researchersif you think they can't handle mapping these Census sheets (which I'm unfamiliar with) to normalised tables. 

You may even find that they can find out more from those normalised tables, as they have the freedom to combine tables
thatthe Census Bureau didn't combine for them. Things like "how much do female Asians make compared to their mobility". 

Alban Hertroys

--
The scale of a problem often equals the size of an ego.