X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 32795D1B4E1 for ; Thu, 25 Sep 2003 12:37:27 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 95248-08 for ; Thu, 25 Sep 2003 09:36:59 -0300 (ADT) Received: from rambutan.pingpong.net (81.milagro.bahnhof.net [195.178.168.81]) by svr1.postgresql.org (Postfix) with ESMTP id 30909D1B541 for ; Thu, 25 Sep 2003 09:36:53 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by rambutan.pingpong.net (8.12.8p1/8.12.8) with ESMTP id h8PCarr7002484 for ; Thu, 25 Sep 2003 14:36:53 +0200 (CEST) (envelope-from girgen@pingpong.net) Date: Thu, 25 Sep 2003 14:36:53 +0200 From: Palle Girgensohn To: pgsql-performance@postgresql.org Subject: performance hit when joining with a view? Message-ID: <31150000.1064493413@rambutan.pingpong.net> X-Mailer: Mulberry/3.0.3 (Linux/x86) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; FORMAT=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/317 X-Sequence-Number: 3683 Hi! A performance question: I have some tables: Tabell "public.person" Kolumn | Typ | Modifierare ------------------+--------------------------+--------------- userid | text | not null giver | text | first_name | text | last_name | text | email | text | default_language | text | default 'sv' created | timestamp with time zone | default now() created_by | text | Index: person_pkey prim=E4rnyckel btree (userid), person_index unik btree (userid), person_giver_idx btree (giver) Fr=E4mmande nyckel-villkor: pp_fk9 FOREIGN KEY (giver) REFERENCES=20 providers(giver) ON UPDATE CASCADE ON DELETE CASCADE, pp_fk2 FOREIGN KEY (created_by) REFERENCES=20 person(userid) ON UPDATE CASCADE ON DELETE SET NULL Tabell "public.wiol" Kolumn | Typ | Modifierare -----------------+-----------------------------+--------------- userid | text | not null course_id | integer | login_ts | timestamp without time zone | default now() latest_event_ts | timestamp without time zone | default now() Fr=E4mmande nyckel-villkor: pp_fk2 FOREIGN KEY (course_id) REFERENCES=20 course(id) ON UPDATE CASCADE ON DELETE CASCADE, pp_fk1 FOREIGN KEY (userid) REFERENCES=20 person(userid) ON UPDATE CASCADE ON DELETE CASCADE and a view: Vy "public.person_wiol_view" Kolumn | Typ | Modifierare ------------------+--------------------------+------------- userid | text | giver | text | first_name | text | last_name | text | email | text | default_language | text | created | timestamp with time zone | created_by | text | course_id | integer | Vydefinition: SELECT p.userid, p.giver, p.first_name, p.last_name, p.email,= =20 p.default_language, p.created, p.created_by, w.course_id FROM (person p=20 LEFT JOIN wiol w ON ((p.userid =3D w.userid))); Now, with about 30000 tuples in person and about 40 in wiol, executing a=20 left outer join with the view gives horrible performance: explain analyze select p.pim_id, p.recipient, p.sender, p.message, p.ts,= =20 p.type, case when sender.userid is not null then sender.first_name || ' '= =20 || sender.last_name else null end as sender_name, sender.course_id is not= =20 null as is_online from pim p left outer join person_wiol_view sender on=20 (sender.userid =3D p.sender) where p.recipient =3D 'axto6551' and p.type >= =3D 0=20 limit 1; QUERY PLAN --------------------------------------------------------------------------- ---------------------------------------------------------- Limit (cost=3D0.00..1331.26 rows=3D1 width=3D180) (actual time=3D866.14..= 1135.65=20 rows=3D1 loops=3D1) -> Nested Loop (cost=3D0.00..1331.26 rows=3D1 width=3D180) (actual=20 time=3D866.13..1135.63 rows=3D2 loops=3D1) Join Filter: ("inner".userid =3D "outer".sender) -> Seq Scan on pim p (cost=3D0.00..0.00 rows=3D1 width=3D112) (a= ctual=20 time=3D0.05..0.18 rows=3D2 loops=3D1) Filter: ((recipient =3D 'axto6551'::text) AND ("type" >=3D 0= )) -> Materialize (cost=3D956.15..956.15 rows=3D30009 width=3D68) (= actual=20 time=3D369.33..437.86 rows=3D22045 loops=3D2) -> Hash Join (cost=3D0.00..956.15 rows=3D30009 width=3D68)= =20 (actual time=3D0.45..605.21 rows=3D30013 loops=3D1) Hash Cond: ("outer".userid =3D "inner".userid) -> Seq Scan on person p (cost=3D0.00..806.09=20 rows=3D30009 width=3D32) (actual time=3D0.16..279.28 rows=3D30009 loops=3D1) -> Hash (cost=3D0.00..0.00 rows=3D1 width=3D36) (act= ual=20 time=3D0.13..0.13 rows=3D0 loops=3D1) -> Seq Scan on wiol w (cost=3D0.00..0.00 rows= =3D1=20 width=3D36) (actual time=3D0.02..0.09 rows=3D8 loops=3D1) Total runtime: 1143.93 msec (12 rader) but rewriting the question with an explicit join uses the indices, and runs= =20 *much* faster: explain analyze select p.pim_id, p.recipient, p.sender, p.message, p.ts,= =20 p.type, case when sender.userid is not null then sender.first_name || ' '= =20 || sender.last_name else null end as sender_name, w.course_id is not null= =20 as is_online from pim p left outer join person sender on (sender.userid =3D= =20 p.sender) left join wiol w on (w.userid=3Dsender.userid) where p.recipient = =3D=20 'axto6551' and p.type >=3D 0 limit 1; QUERY PLAN --------------------------------------------------------------------------- ----------------------------------------------------------------- Limit (cost=3D0.00..6.03 rows=3D1 width=3D180) (actual time=3D0.89..1.13 = rows=3D1=20 loops=3D1) -> Hash Join (cost=3D0.00..6.03 rows=3D1 width=3D180) (actual=20 time=3D0.88..1.12 rows=3D2 loops=3D1) Hash Cond: ("outer".userid =3D "inner".userid) -> Nested Loop (cost=3D0.00..6.02 rows=3D1 width=3D144) (actual= =20 time=3D0.48..0.69 rows=3D2 loops=3D1) -> Seq Scan on pim p (cost=3D0.00..0.00 rows=3D1 width=3D1= 12)=20 (actual time=3D0.04..0.16 rows=3D2 loops=3D1) Filter: ((recipient =3D 'axto6551'::text) AND ("type" = >=3D=20 0)) -> Index Scan using person_pkey on person sender=20 (cost=3D0.00..6.01 rows=3D1 width=3D32) (actual time=3D0.23..0.24 rows=3D1 = loops=3D2) Index Cond: (sender.userid =3D "outer".sender) -> Hash (cost=3D0.00..0.00 rows=3D1 width=3D36) (actual=20 time=3D0.22..0.22 rows=3D0 loops=3D1) -> Seq Scan on wiol w (cost=3D0.00..0.00 rows=3D1 width=3D= 36)=20 (actual time=3D0.12..0.17 rows=3D8 loops=3D1) Total runtime: 1.39 msec (11 rader) Tests run on postgresql-7.3.4. Main question is, is it bad SQL to join with a view, or is it postgresql=20 that does something not quite optimal? If the latter, is it fixed in 7.4? Thanks, Palle