Re: That killer 3rd join...

On Thu, Sep 07, 2000 at 02:11:38PM +0100, Oliver Smith wrote:
> However, when I did that, postgres went away. I'll give your idea a try,
> it looks quite promising.

I still found this took quite a long time to process the query. So - I created
the four views, silver, elec, gold, plat, and a new view, jewellery, which
uses those four directly.

Here's the explain result:

EXPLAIN SELECT * FROM jewellery ORDER BY stone_uid ;

NOTICE: QUERY PLAN:

Sort (cost=22.93..22.93 rows=1 width=236)
-> Nested Loop (cost=11.29..22.92 rows=1 width=236)
-> Nested Loop (cost=11.29..21.81 rows=1 width=220)
-> Nested Loop (cost=11.29..19.78 rows=1 width=196)
-> Nested Loop (cost=11.29..18.67 rows=1 width=180)
-> Nested Loop (cost=11.29..17.56 rows=1 width=164)
-> Nested Loop (cost=11.29..16.50 rows=1 width=156)
-> Nested Loop (cost=11.29..14.47 rows=1 width=132)
-> Nested Loop (cost=11.29..13.41 rows=1 width=124)
-> Merge Join (cost=11.29..11.38 rows=1 width=100)
-> Sort (cost=10.18..10.18 rows=2 width=84)
-> Hash Join (cost=3.49..10.16 rows=2 width=84)
-> Nested Loop (cost=0.00..3.91 rows=27 width=60)
-> Seq Scan on metal_types mt (cost=0.00..1.05 rows=1 width=8)
-> Materialize (cost=2.59..2.59 rows=27 width=52)
-> Nested Loop (cost=0.00..2.59 rows=27 width=52)
-> Seq Scan on metal_types mt (cost=0.00..1.05 rows=1 width=8)
-> Seq Scan on stone_types st (cost=0.00..1.27 rows=27 width=44)
-> Hash (cost=2.08..2.08 rows=108 width=24)
-> Seq Scan on jewellery_combinations jc (cost=0.00..2.08 rows=108 width=24)
-> Sort (cost=1.11..1.11 rows=5 width=16)
-> Seq Scan on jewellery_types jt (cost=0.00..1.05 rows=5 width=16)
-> Index Scan using jewellery_combinations_pkey on jewellery_combinations jc (cost=0.00..2.01 rows=1 width=24)
-> Seq Scan on metal_types mt (cost=0.00..1.05 rows=1 width=8)
-> Index Scan using jewellery_combinations_pkey on jewellery_combinations jc (cost=0.00..2.01 rows=1 width=24)
-> Seq Scan on metal_types mt (cost=0.00..1.05 rows=1 width=8)
-> Seq Scan on jewellery_types jt (cost=0.00..1.05 rows=5 width=16)
-> Seq Scan on jewellery_types jt (cost=0.00..1.05 rows=5 width=16)
-> Index Scan using jewellery_combinations_pkey on jewellery_combinations jc (cost=0.00..2.01 rows=1 width=24)
-> Seq Scan on jewellery_types jt (cost=0.00..1.05 rows=5 width=16)

EXPLAIN

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