pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Charles Hornberger <hornberger@tabloid.net>
To: pgsql-sql@postgresql.org
Subject: very slow response time on large, multi-table view
Date: Wed, 13 Jan 1999 17:23:40 -0800
Message-ID: <3.0.5.32.19990113172340.00a246d0@tabloid.net> (raw)

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




Message-ID: <3.0.5.32.19990113172340.00a246d0@tabloid.net>
Permalink:  ../3.0.5.32.19990113172340.00a246d0@tabloid.net/
Also on:    postgresql.org/message-id/3.0.5.32.19990113172340.00a246d0@tabloid.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: hornberger@tabloid.net
  Subject: Re: very slow response time on large, multi-table view
  In-Reply-To: <3.0.5.32.19990113172340.00a246d0@tabloid.net>
  References: <3.0.5.32.19990113172340.00a246d0@tabloid.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox