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
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