That killer 3rd join...

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.


If you do:

 SELECT * FROM jcombo_query WHERE metal_uid = 1 ;
or
 SELECT * FROM jcombo_query jq, metal_types mt
  WHERE mt.metal_name = 'Silver' AND mt.metal_uid = jq.metal_uid ;

There is no delay.

Also doing

 SELECT * from jcombo_query silv, jcombo_query elec
  WHERE silv.metal_uid = 1 AND elec.metal_uid = 2 ;

Still no delay. But add a third join

 SELECT * from jcombo_query silv, jcombo_query elec, jcombo_query gold
  WHERE silv.metal_uid = 1 AND elec.metal_uid = 2 AND gold.metal_uid = 3;

Add a fourth:

 SELECT * from jcombo_query silv, jcombo_query elec, jcombo_query gold,
               jcombo_query plat
  WHERE silv.metal_uid = 1 AND elec.metal_uid = 2 AND gold.metal_uid = 3 AND
        plat.metal_uid = 4 ;

And it's painful.

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?


Oliver
--
If at first you don't succeed, skydiving is not for you...