Re: That killer 3rd join...

Just played with this a little bit, and I'm not 100% certain whether I'm
getting the "desired results", but why not do something like:

SELECT
stone_name, st.stone_uid, stone_modifies, stone_difficulty, stone_cost,
silver.jtyp_name AS silver_name, (silver.metal_cost + stone_cost) AS Expr1,
silver.jcombo_stats AS silver_stats,
elec.jtyp_name AS electrum_name, (elec.metal_cost + stone_cost) AS Expr2,
elec.jcombo_stats AS electrum_stats,
gold.jtyp_name AS gold_name, (gold.metal_cost + stone_cost) AS Expr3,
gold.jcombo_stats AS gold_stats,
plat.jtyp_name AS plat_name, (plat.metal_cost + stone_cost) AS Expr4,
plat.jcombo_stats AS plat_stats
FROM
stone_types st, silver, elec, gold, plat
WHERE
st.stone_uid = silver.stone_uid AND
st.stone_uid = elec.stone_uid AND
st.stone_uid = gold.stone_uid AND
st.stone_uid = plat.stone_uid ;

where 'silver','elec','gold','plat' are each:

CREATE VIEW silver AS
SELECT
metal_name, jtyp_name, metal_cost, jc.stone_uid, mt.metal_uid, jc.jcombo_stats
FROM
jewellery_types jt, jewellery_combinations jc, metal_types mt
WHERE
jt.jtyp_uid = jc.jtyp_uid AND jc.metal_uid = mt.metal_uid AND mt.metal_uid = 1;

I have to be missing something though, since I'm only getting back 23
results, but they come back quick as anything ...

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

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org