Re: SEVEN cross joins?!?!? - Mailing list pgsql-sql

From Richard Huxton
Subject Re: SEVEN cross joins?!?!?
Date
Msg-id 434BB042.7000309@archonet.com
Whole thread Raw
In response to SEVEN cross joins?!?!?  (Frank Bax <fbax@sympatico.ca>)
Responses Re: SEVEN cross joins?!?!?
List pgsql-sql
Frank Bax wrote:
> I have a table with only 434 rows in it.  Two important columns are 
> "itemid" and "locn".  Each item must be in one of seven locations.  We 
> need to create a "combo" by selecting one item from each of seven 
> locations; then determine which "combo" is the "best" according to our 
> analysis (see below).
> 
> A subselect for items in a location looks something like:
> (select * from suit_item where locn='Head' AND username='Walter' ORDER 
> BY itemid LIMIT 10) as Head
> 
> One subselect for each location, cross join them all and the query 
> generates 10,000,000 combinations!  Without the "LIMIT 10",  there are  
> 78 * 37 * 91 * 81 * 99 * 47 * 1 = 98,981,901,018 results returned for 
> username='Walter' (the only user at the moment).  The large volume is 
> causing a problem for my systems!  The "ORDER BY itemid" was added only 
> so that same 10 items were processed on different computer systems I 
> tested this query on.  Only one item for 7th locn in the database at the 
> moment.

Frank - it might just be me, but I've read your email twice and despite 
all the information I still don't have any idea what you are trying to do.

Are you saying that you WANT to generate a cross-join, score the 
millions of results and then pick the best 10? It's doing what you want, 
but you'd like it to be faster.

Or are you saying that you'd like to avoid the explosion in rows altogether?

In either case - I don't suppose you could provide a real example of the 
query, so we can see exactly what you're trying to do.
--  Richard Huxton  Archonet Ltd


pgsql-sql by date:

Previous
From: Frank Bax
Date:
Subject: SEVEN cross joins?!?!?
Next
From: "Rick Schumeyer"
Date:
Subject: question re. count, group by, and having