Re: That killer 3rd join...

For the record, I have a similar query in a program I developed that
aliases the same table multiple times and the performance on that one is
terrible as well. In fact, it's the only query of the many that program
uses that isn't downright snappy. Short of a table redesign (which I may
do anyway), I haven't found a way of eliminating the bottleneck. Here's
a snippet of the query...

SELECT ...
FROM ...
review_statuses g,
review_statuses h,
review_statuses i,
review_statuses j,
review_statuses k,
review_statuses l,
review_statuses m,
review_statuses n
WHERE ...
and a.review_status_wsep = g.status_code
and a.review_status_dec = h.status_code
and a.review_status_mar = i.status_code
and a.review_status_jun = j.status_code
and b.review_status_wsep = k.status_code
and b.review_status_dec = l.status_code
and b.review_status_mar = m.status_code
and b.review_status_jun = n.status_code
ORDER BY ...;

Similarly, the EXPLAIN is extraordinarily long, so I haven't included.
I've not run this particular query on our Oracle database (when I get a
little free time I will), but have run similar types of queries and have
never noticed any substantial performance hit, so I suspect that there's
an opportunity for optimization here.

Incidentally, and I probably should have mentioned it sooner than now,
the program of which this query is a part won a CIO Magazine Web
Business 50/50 Award this past July (IEPManager). Unfortunately, I gave
the magazine lots of information which they reduced to a very short
write-up. Included in that information was the fact that the project was
build upon and runs on PostgreSQL (wanted to plug the developers' fine
work), first v6.5.3 and now v7.0.2 (which is much improved -- many
thanks). I had played with the earlier 6.4.x releases but did not find
them robust enough for production use (we've been using Oracle since
v5.something and, other than some early v6.x problems, have always
appreciated its robustness, so our expectations are relatively high).
Anyway, we needed to go to production use with IEPManager way faster
than we had anticipated and therefore didn't have any budget for the
project. So, we planned to start development on PostgreSQL then port to
Oracle when funds were available (we're an underfunded not for profit
organization). We are so pleased with the performance of PostgreSQL,
though, that we've scrapped the porting plans. In fact, we've since done
another project on PostgreSQL and look forward to all the great stuff
that's planned for future releases (schemas and outer joins,
especially). Thanks to all of the core developers for an absolutely
superb job!

- Bill

Stephan Szabo wrote:
> OTOH, I'm not 100% sure what you're trying to get out
> from this query, I'd have expected that it would be, using
> these metals on this stone gives you this result, but since
> the types of jewelery I get are different on the same row
> of output, I'm a little confused.
>
> Stephan Szabo
> sszabo(at)bigpanda(dot)com
>
> On Mon, 4 Sep 2000, Oliver Smith wrote:
>
> > In order to explore some postgres performance options with table
> > collation, I decided to use a little experimental dabase to try out
> > some of the options I saw. What I want to create queries to combine
> > data from 2+ tables into individual rows.
> >
> > So - being a bit of an EQ player, I cobbled together a trivial little
> > database that tries to generate an 'EQ Jewellery' table. It all works
> > fine, and it works fine under MS Access or mysql. But under Postgres,
> > it grinds. It chugs.
> >
> > When I experimented with the database, I found that it only started to
> > do this when I go to a fourth level of join.
> >
> > The database can be found here:
> > http://www.kfs.org/~oliver/jewellery/dbcreate.sql Definition
> > http://www.kfs.org/~oliver/jewellery/insert.sql Insert statements
> >
> > As you'll see - it's a pretty small table.
> >
> >
> > So naturally, when I add the join (stone_types.stone_uid):
> >
> > SELECT * from jcombo_query silv, jcombo_query elec, jcombo_query gold,
> > jcombo_query plat, stone_types st
> > WHERE silv.metal_uid = 1 AND silv.stone_uid = st.stone_uid AND
> > elec.metal_uid = 2 AND elec.stone_uid = st.stone_uid AND
> > gold.metal_uid = 3 AND gold.stone_uid = st.stone_uid AND
> > plat.metal_uid = 4 AND plat.stone_uid = st.stone_uid ;
> >
> > It takes way way way too long to come back for such a small database.
> >
> > How can I improve upon this kind of query?

--
Bill Sofko
Publisher, Contrariwise