pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Patnude <gpatnude@hotmail.com>
To: 'Frank Bax' <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: SEVEN cross joins?!?!?
Date: Tue, 11 Oct 2005 15:14:30 -0700
Message-ID: <BAY105-DAV14FDE0F61E8B57420D4A8ED7780@phx.gbl> (raw)
In-Reply-To: <5.2.1.1.0.20051011145705.00a31440@pop6.sympatico.ca>

Aha ! A gamer... playing with armor and hit points and things.... 




-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Frank Bax
Sent: Tuesday, October 11, 2005 1:06 PM
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] SEVEN cross joins?!?!?

At 08:29 AM 10/11/05, Richard Huxton wrote:

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


There is no "best 10".  I currently limit each subselect to 10 items so 
that query will actually run without crashing.  I would like to remove the 
"ORDER BY itemid LIMIT 10" mentioned above.  At the end of the query I have 
a "LIMIT 100" clause which will stay and produces a list of "best 100"
combos.

Either of your solutions would be acceptable; since avoiding the 
"explosion" would also make the query faster.  Current calculations 
indicate that running the query without "LIMIT 10" in subselect would take 
years to process.

The query is filled with expressions.  I'm not sure I can shorten it 
without making typos or deleting something important, so I'll make it 
available on web here:
         http://www.execulink.com/~fbax/JOINS/
Results of "explain analyse" is also there. 


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match



view thread (10+ messages)  latest in thread

Message-ID: <BAY105-DAV14FDE0F61E8B57420D4A8ED7780@phx.gbl>
Permalink:  ../BAY105-DAV14FDE0F61E8B57420D4A8ED7780@phx.gbl/
Also on:    postgresql.org/message-id/BAY105-DAV14FDE0F61E8B57420D4A8ED7780@phx.gbl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: gpatnude@hotmail.com, fbax@sympatico.ca
  Subject: Re: SEVEN cross joins?!?!?
  In-Reply-To: <BAY105-DAV14FDE0F61E8B57420D4A8ED7780@phx.gbl>
  References: <5.2.1.1.0.20051011145705.00a31440@pop6.sympatico.ca> <BAY105-DAV14FDE0F61E8B57420D4A8ED7780@phx.gbl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox