Re: [SQL] To use a VIEW or not to use a View.....

[Tomasz Myrta]

Ries van Twisk wrote:

First of all I want to thank you for all responses! I was overwhelmed with
it :D

Below you find the schema I'm currently using and the output of explain. I
removed all comments so the mail will be small, the schema is still work in
progress. I especially I need to take a look at the indexes. Any hints will
be appreciated.

best reghards,
Ries van Twisk


<-----------
Here you find the output of the explain again:
I cannot yet read the output of explain si I'm not sure if the output looks
good or bad.

It looks like your cablelist table doesn't contain too many records, so result is inacurate. Postgresql doesn't use indexes if you have too
little rows.
First look on your explain is ok, your query should work fine if tables
are well indexed.
Make additional tests with tables containing more rows, "explain analyze"
helps a bit, because it shows real times.

Tomasz Myrta

echo "VACUUM ANALYZE; EXPLAIN SELECT * FROM full_cablelist WHERE
projectcode=5" | psql testdb > /tmp/explain.txt

NOTICE:  QUERY PLAN:

Hash Join  (cost=26.28..39.00 rows=23 width=200)
  ->  Hash Join  (cost=24.85..37.17 rows=23 width=182)
        ->  Hash Join  (cost=23.43..35.34 rows=23 width=164)
              ->  Seq Scan on libitems lit  (cost=0.00..7.39 rows=339
width=27)
              ->  Hash  (cost=23.37..23.37 rows=23 width=137)
                    ->  Hash Join  (cost=11.05..23.37 rows=23 width=137)
                          ->  Hash Join  (cost=9.75..21.67 rows=23
width=120)
                                ->  Seq Scan on libitems lif
(cost=0.00..7.39 rows=339 width=27)
                                ->  Hash  (cost=9.69..9.69 rows=23 width=93)
                                      ->  Hash Join  (cost=4.76..9.69
rows=23 width=93)
                                            ->  Hash Join  (cost=3.46..7.99
rows=23 width=76)
                                                  ->  Hash Join
(cost=2.42..6.32 rows=69 width=63)
                                                        ->  Seq Scan on
cablelist cl  (cost=0.00..2.69 rows=69 width=41)
                                                        ->  Hash
(cost=2.06..2.06 rows=106 width=22)
                                                              ->  Seq Scan
on cabletypes ct  (cost=0.00..2.06 rows=106 width=22)
                                                  ->  Hash  (cost=1.04..1.04
rows=1 width=13)
                                                        ->  Seq Scan on
projectcodes pc  (cost=0.00..1.04 rows=1 width=13)
                                            ->  Hash  (cost=1.24..1.24
rows=24 width=17)
                                                  ->  Seq Scan on
libconnections lcf  (cost=0.00..1.24 rows=24 width=17)
                          ->  Hash  (cost=1.24..1.24 rows=24 width=17)
                                ->  Seq Scan on libconnections lct
(cost=0.00..1.24 rows=24 width=17)
        ->  Hash  (cost=1.34..1.34 rows=34 width=18)
              ->  Seq Scan on shiplocations slt  (cost=0.00..1.34 rows=34
width=18)
  ->  Hash  (cost=1.34..1.34 rows=34 width=18)
        ->  Seq Scan on shiplocations slf  (cost=0.00..1.34 rows=34
width=18)

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly