very slow response time on large, multi-table view - Mailing list pgsql-general

Could anyone help me figure out why SELECTing data from a pretty complex
view is happening incredibly slowly on Postgres?  I'm porting a database
from Solid to Postgres, and had to rewrite the following view from Solid:

  CREATE VIEW FRONT_VIEW AS
    SELECT A.ARTICLE_ID, S.SUBJECT, A.HEADLINE, A.SUBHEAD, A.BYLINE,
      A.COUNTRY_ID, A.PUBL_DATE,
      A.ARTICLE_TEXT,
      AC.ART_SRC_DISPLAY, AT.ARTICLE_TYPE, D.DATELINE, F.PRIORITY
    FROM FRONTPAGE F, ARTICLES A, SUBJECTS S, ARTICLE_TYPES AT, DATELINE D
      LEFT JOIN ARTICLES A ON F.ARTICLE_ID = A.ARTICLE_ID
      LEFT JOIN ARTICLE_SOURCES AC ON A.ART_SRC_ID = AC.ART_SRC_ID
      LEFT JOIN ARTICLE_TYPES AT ON A.ART_TYPE_ID = AT.ART_TYPE_ID
      LEFT JOIN DATELINES D ON A.DATELINE_ID = D.DATELINE_ID
      LEFT JOIN SUBJECTS S ON A.SUBJECT1_ID = S.SUBJECT_ID;

I rewrote it like so under Postgres:

 CREATE VIEW FRONT_VIEW AS
   SELECT
     A.ARTICLE_ID, A.HEADLINE, A.SUBHEAD, A.BYLINE, A.COUNTRY_ID,
A.PUBL_DATE, A.ARTICLE_TEXT,
     AC.ART_SRC_DISPLAY, AT.ARTICLE_TYPE, D.DATELINE,
     F.PRIORITY
   FROM
     FRONTPAGE F, ARTICLES A, ARTICLE_SOURCES AC, ARTICLE_TYPES AT,
DATELINES D, SUBJECTS S
   WHERE F.ARTICLE_ID = A.ARTICLE_ID         -- both are indexed
     AND AC.ART_SRC_ID = A.ART_SRC_ID        -- ac.art_src_id is indexed
     AND AT.ART_TYPE_ID = A.ART_TYPE_ID      -- at.art_type_id is indexed
     AND D.DATELINE_ID = A.DATELINE_ID       -- both are indexed
     AND S.SUBJECT_ID = A.SUBJECT1_ID;      -- s.subject_id is indexed


I was surprised to see that when doing a SELECT * FROM FRONT_VIEW; in psql,
the database took nearly 15 seconds to execute the query.  I vacuumed the
DB (although there's almost nothing in it), and tried again -- but got the
same result.  Running the same query in the Solid sql monitor produced an
almost instantaneous response.

Finally, I did an "explain" on the query.  But I have to confess I don't
know how to interpret the output:

apx2=> explain select * from front_view;
NOTICE:  QUERY PLAN:

Nested Loop  (cost=10.36 size=2 width=116)
  ->  Nested Loop  (cost=8.30 size=2 width=114)
        ->  Nested Loop  (cost=6.23 size=2 width=100)
              ->  Nested Loop  (cost=4.16 size=2 width=86)
                    ->  Nested Loop  (cost=2.10 size=2 width=72)
                          ->  Seq Scan on frontpage f  (cost=1.03 size=1
width=6)
                          ->  Seq Scan on articles a  (cost=1.07 size=2
width=66)
                    ->  Seq Scan on datelines d  (cost=1.03 size=1 width=14)
              ->  Seq Scan on article_sources ac  (cost=1.03 size=1 width=14)
        ->  Seq Scan on article_types at  (cost=1.03 size=1 width=14)
  ->  Seq Scan on subjects s  (cost=1.03 size=1 width=2)

EXPLAIN

Solid's "explain" tool generated the following output:

explain plan for select * from front_view;
       ID   UNIT_ID    PAR_ID JOIN_PATH UNIT_TYPE          INFO
       --   -------    ------ --------- ---------          ----
        1         1         0         2 JOIN UNIT
        2         2         1         3 JOIN UNIT          LOOP JOIN
        3         2         1        12
        4         3         2         4 JOIN UNIT          LOOP JOIN
        5         3         2        11
        6         4         3         5 JOIN UNIT          LOOP JOIN
        7         4         3        10
        8         5         4         6 JOIN UNIT          LOOP JOIN
        9         5         4         9
       10         6         5         7 JOIN UNIT          LOOP JOIN
       11         6         5         8
       12         7         6         0 TABLE UNIT         FRONTPAGE
       13         7         6         0                    SCAN TABLE
       14         8         6         0 TABLE UNIT         ARTICLES
       15         8         6         0                    PRIMARY KEY
       16         8         6         0                    ARTICLE_ID = ...
       17         9         5         0 TABLE UNIT         ARTICLE_SOURCES
       18         9         5         0                    PRIMARY KEY
       19         9         5         0                    ART_SRC_ID = ...
       20        10         4         0 TABLE UNIT         ARTICLE_TYPES
       21        10         4         0                    PRIMARY KEY
       22        10         4         0                    ART_TYPE_ID = ...
       23        11         3         0 TABLE UNIT         CITY_DATELINES
       24        11         3         0                    PRIMARY KEY
       25        11         3         0                    CITY_DATELINE_ID
= ...
       26        12         2         0 TABLE UNIT         SUBJECTS
       27        12         2         0                    PRIMARY KEY
       28        12         2         0                    SUBJECT_ID = ...
28 rows fetched.


I'm running Postgres 6.4 on Linux 2.0.35 on a Pentium 133 with a bunch of
RAM (I think it's 192MB).

Thanks in advance for any advice.

Charlie