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

Hi.  I asked this the question below in pgsql-general a couple days ago and
got no reply, so I'm wondering if maybe it's more appropriately directed at
this list and that's why it went unanswered.  So here goes again.  Any help
would be very much appreciated.

Thanks,
Charlie

ORIGINAL MESSAGE:
-----------------

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