Unused table of view - Mailing list pgsql-performance

Hello,

I'm using postgresql 7.4.2, and I have this view:
slooze=# \d userpictures2
     Vue «public.userpictures2»
   Colonne   |           Type           | Modificateurs
-------------+--------------------------+---------------
 pictureid   | integer                  |
 rollid      | character varying(64)    |
 frameid     | character varying(64)    |
 description | character varying(255)   |
 filename    | character varying(255)   |
 owner       | integer                  |
 entrydate   | timestamp with time zone |
 date        | timestamp with time zone |
 nbclick     | integer                  |
 nbrates     | integer                  |
 maxrate     | smallint                 |
 minrate     | smallint                 |
 averagerate | real                     |
 sumrates    | integer                  |
 userid      | integer                  |
Définition de la vue
 SELECT DISTINCT ON (permissions.pictureid, userid) pictures.pictureid, rollid, frameid, description, filename,
"owner",entrydate, date, nbclick, nbrates, maxrate, minrate, averagerate, sumrates, userid 
   FROM permissions
   JOIN groupsdef USING (groupid)
   JOIN pictures USING (pictureid)
  WHERE groupsdef.groupid = permissions.groupid
  ORDER BY permissions.pictureid, userid;


Now consider this query:

SELECT count(*) FROM userpictures JOIN topicscontent using(PictureID) WHERE TopicID=137 and UserID=2;

The pictures table is scanned, but it's not needed. (see plan at the end).

I believe it's not need because my tables are as follow:

CREATE TABLE pictures (
    PictureID serial PRIMARY KEY,
    RollID character varying(64) NOT NULL REFERENCES rolls,
    FrameID character varying(64) NOT NULL,
    Description character varying(255),
    Filename character varying(255),
    Owner integer NOT NULL REFERENCES users,
    EntryDate datetime DEFAULT now(),
    Date datetime,
    NbClick integer DEFAULT 0,
    NbRates integer DEFAULT 0,
    MaxRate int2,
    MinRate int2,
    AverageRate float4 DEFAULT 5,
    SumRates integer DEFAULT 0);

CREATE TABLE permissions (
    GroupID integer NOT NULL REFERENCES groups ON DELETE cascade,
    PictureID integer NOT NULL REFERENCES pictures ON DELETE cascade,
    UNIQUE (GroupID, PictureID));

CREATE TABLE groupsdef (
    UserID integer REFERENCES users,
    GroupID integer REFERENCES groups,
    PRIMARY KEY (UserID,GroupID));

CREATE TABLE topicscontent (
    TopicID integer REFERENCES topics ON DELETE cascade,
    PictureID integer REFERENCES pictures ON DELETE cascade,
    Direct boolean NOT NULL,
    PRIMARY KEY (TopicID,PictureID) );

So obviously, the join on pictures is not adding any rows, since
permissions.PictureID references pictures.PictureID and
pictures.PictureID is the primary key.

I can workaround with a second view:

slooze=# \d userpictures2
     Vue «public.userpictures2»
  Colonne  |  Type   | Modificateurs
-----------+---------+---------------
 pictureid | integer |
 userid    | integer |
Définition de la vue
 SELECT DISTINCT pictureid, userid
   FROM permissions
   JOIN groupsdef USING (groupid)
  WHERE groupsdef.groupid = permissions.groupid
  ORDER BY pictureid, userid;

But it would be better if Postgresql could figure it out itself. Is
there a way to currently avoid the 2nd view ?

QUERY PLAN for SELECT count(*) FROM userpictures JOIN topicscontent using(PictureID) WHERE TopicID=137 and UserID=2;
--------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1195.15..1195.15 rows=1 width=0) (actual time=89.252..89.253 rows=1 loops=1)
   ->  Merge Join  (cost=1096.05..1194.98 rows=66 width=0) (actual time=84.574..89.202 rows=8 loops=1)
         Merge Cond: ("outer".pictureid = "inner".pictureid)
         ->  Subquery Scan userpictures  (cost=995.78..1081.47 rows=4897 width=4) (actual time=84.386..88.530 rows=841
loops=1)
               ->  Unique  (cost=995.78..1032.50 rows=4897 width=105) (actual time=84.377..87.803 rows=841 loops=1)
                     ->  Sort  (cost=995.78..1008.02 rows=4897 width=105) (actual time=84.369..84.786 rows=1433
loops=1)
                           Sort Key: permissions.pictureid, groupsdef.userid
                           ->  Hash Join  (cost=371.82..695.65 rows=4897 width=105) (actual time=23.328..56.498
rows=5076loops=1) 
                                 Hash Cond: ("outer".pictureid = "inner".pictureid)
                                 ->  Index Scan using pictures_pkey on pictures  (cost=0.00..164.87 rows=2933 width=97)
(actualtime=0.015..4.591 rows=2933 loops=1) 
                                 ->  Hash  (cost=359.58..359.58 rows=4897 width=8) (actual time=23.191..23.191 rows=0
loops=1)
                                       ->  Merge Join  (cost=10.16..359.58 rows=4897 width=8) (actual
time=0.110..19.365rows=5076 loops=1) 
                                             Merge Cond: ("outer".groupid = "inner".groupid)
                                             ->  Sort  (cost=10.16..10.19 rows=12 width=8) (actual time=0.080..0.088
rows=11loops=1) 
                                                   Sort Key: groupsdef.groupid
                                                   ->  Index Scan using groupsdef_userid_key on groupsdef
(cost=0.00..9.94rows=12 width=8) (actual time=0.038..0.056 rows=11 loops=1) 
                                                         Index Cond: (userid = 2)
                                             ->  Index Scan using permissions_groupid_key on permissions
(cost=0.00..279.63rows=8305 width=8) (actual time=0.015..9.801 rows=7633 loops=1) 
         ->  Sort  (cost=100.28..100.37 rows=38 width=4) (actual time=0.114..0.118 rows=8 loops=1)
               Sort Key: topicscontent.pictureid
               ->  Index Scan using topicscontent_topicid on topicscontent  (cost=0.00..99.28 rows=38 width=4) (actual
time=0.052..0.072rows=8 loops=1) 
                     Index Cond: (topicid = 137)
 Total runtime: 91.096 ms


QUERY PLAN for SELECT count(*) FROM userpictures JOIN topicscontent using(PictureID) WHERE TopicID=137 and UserID=2;
--------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=859.09..859.09 rows=1 width=4) (actual time=30.488..30.489 rows=1 loops=1)
   ->  Merge Join  (cost=759.99..858.92 rows=66 width=4) (actual time=27.845..30.466 rows=8 loops=1)
         Merge Cond: ("outer".pictureid = "inner".pictureid)
         ->  Subquery Scan userpictures2  (cost=659.71..745.41 rows=4897 width=4) (actual time=27.707..29.853 rows=841
loops=1)
               ->  Unique  (cost=659.71..696.44 rows=4897 width=8) (actual time=27.701..29.121 rows=841 loops=1)
                     ->  Sort  (cost=659.71..671.95 rows=4897 width=8) (actual time=27.696..28.153 rows=1433 loops=1)
                           Sort Key: permissions.pictureid, groupsdef.userid
                           ->  Merge Join  (cost=10.16..359.58 rows=4897 width=8) (actual time=0.101..20.682 rows=5076
loops=1)
                                 Merge Cond: ("outer".groupid = "inner".groupid)
                                 ->  Sort  (cost=10.16..10.19 rows=12 width=8) (actual time=0.074..0.078 rows=11
loops=1)
                                       Sort Key: groupsdef.groupid
                                       ->  Index Scan using groupsdef_userid_key on groupsdef  (cost=0.00..9.94 rows=12
width=8)(actual time=0.035..0.055 rows=11 loops=1) 
                                             Index Cond: (userid = 2)
                                 ->  Index Scan using permissions_groupid_key on permissions  (cost=0.00..279.63
rows=8305width=8) (actual time=0.014..10.093 rows=7633 loops=1) 
         ->  Sort  (cost=100.28..100.37 rows=38 width=4) (actual time=0.091..0.094 rows=8 loops=1)
               Sort Key: topicscontent.pictureid
               ->  Index Scan using topicscontent_topicid on topicscontent  (cost=0.00..99.28 rows=38 width=4) (actual
time=0.039..0.057rows=8 loops=1) 
                     Index Cond: (topicid = 137)
 Total runtime: 31.376 ms

--
Laurent Martelli
laurent@aopsys.com                                Java Aspect Components
http://www.aopsys.com/                          http://jac.objectweb.org