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
