Re: Views- Advantages and Disadvantages

On Wednesday 9. May 2007 06:32, Ashish Karalkar wrote:
>Hello All,
>
>Can anybody please point me to Advantages and Disadvantages of using
> view

Sometimes, a view can fool you into writing hideously expensive queries
just because it is the first method that comes to mind.

I upgraded to version 8.2.4 a few days ago, and haven't gotten around to
change the memory settings. Thus, I just discovered that my pedigree
drawing script seemed to hang forever. I finally let it run its course,
and it clocked in on about two minutes. In a db where the largest table
is about 50,000 rows, this is ridiculous. I opened the script and
attacked the first query I found. It will find the children of the
focus person and list them ordered by birth date:

    $query = "select person_id, pb_date from tmg_persons
                where father_id = $p or mother_id = $p
                order by pb_date";

tmg_persons is a view involving several function calls, and is a legacy
from an earlier, flatter data model where the 'persons' table actually
had this structure. I'm still using it in my Web application, and the
primary function of the view is to make an easy export:

CREATE OR REPLACE VIEW tmg_persons AS
SELECT
    person_id,
    get_parent(person_id,1) AS father_id,
    get_parent(person_id,2) AS mother_id,
    last_edit,
    get_pbdate(person_id) AS pb_date,
    get_pddate(person_id) AS pd_date,
    gender AS s,
    living AS l,
    is_public AS p
FROM persons;

I ran an "explain select" on the query:

pgslekt=> explain select person_id, pb_date from tmg_persons where
father_id=1130;
                             QUERY PLAN
--------------------------------------------------------------------
 Subquery Scan tmg_persons  (cost=0.00..729.06 rows=81 width=36)
   Filter: (father_id = 1130)
   ->  Seq Scan on persons  (cost=0.00..525.96 rows=16248 width=19)
(3 rows)

Sequential scans usually spell Big Trouble. So, I rewrote the query to
read directly from the 'relations' table:

    $query = "select child_fk, get_pbdate(child_fk) as pb_date
                from relations
                where parent_fk = $p
                order by pb_date";

pgslekt=> explain select child_fk, get_pbdate(child_fk) as pb_date from
relations where parent_fk=1130 order by pb_date;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Sort  (cost=150.52..150.81 rows=117 width=4)
   Sort Key: get_pbdate(child_fk)
   ->  Bitmap Heap Scan on relations  (cost=5.16..146.50 rows=117
width=4)
         Recheck Cond: (parent_fk = 1130)
         ->  Bitmap Index Scan on parent_key  (cost=0.00..5.13 rows=117
width=0)
               Index Cond: (parent_fk = 1130)
(6 rows)

And that was it. The script now runs in about 1/10 of a second.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/