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

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.05rows=1 width=8) 
                                                                          ->  Materialize  (cost=2.59..2.59 rows=27
width=52)
                                                                                ->  Nested Loop  (cost=0.00..2.59
rows=27width=52) 
                                                                                      ->  Seq Scan on metal_types mt
(cost=0.00..1.05rows=1 width=8) 
                                                                                      ->  Seq Scan on stone_types st
(cost=0.00..1.27rows=27 width=44) 
                                                                    ->  Hash  (cost=2.08..2.08 rows=108 width=24)
                                                                          ->  Seq Scan on jewellery_combinations jc
(cost=0.00..2.08rows=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=5width=16) 
                                                  ->  Index Scan using jewellery_combinations_pkey on
jewellery_combinationsjc  (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.01rows=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...