Message-ID: <3E2FC8C9.9090509@klaster.net>
Date: Thu, 23 Jan 2003 11:49:45 +0100
From: Tomasz Myrta <jasiek@klaster.net>
User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; PL; rv:1.1) Gecko/20020826
MIME-Version: 1.0
To: Ries van Twisk <ries@jongert.nl>
Cc: pgsql-sql@postgresql.org
Subject: Re: To use a VIEW or not to use a View.....
References: <001001c2c2b6$9abbcab0$f100000a@IT001>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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)