Re: That killer 3rd join... - Mailing list pgsql-general

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@hub.org           secondary: scrappy@{freebsd|postgresql}.org