Re: That killer 3rd join...

I'd put the explain output in, but it's just too too long.
I think the issue here is that you're actually doing alot more
joins than you think you are. (I think it's like 13 or so
after the rewriting - 3 for each copy of the view)
Your best bet is to put the results of jcombo_query into a
temporary table and then join that 4 times rather than the
12 joins it turns out to in your query below.

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.

As a separate issue I can't even do the query listed below on my
machine with reasonably current sources, as it fails after a while with
an ExecRestrPos: node type 18 not supported. Haven't looked yet...

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?