X-Original-To: pgsql-sql@postgresql.org Received: from shield.pirmabanka.lv (unknown [195.114.57.4]) by postgresql.org (Postfix) with ESMTP id 8CA7F47610C for ; Mon, 10 Mar 2003 08:51:33 -0500 (EST) Received: from wall2.pirmabanka.lv (wall2-pub.pirmabanka.lv [195.114.57.14]) by shield.pirmabanka.lv (8.12.8/8.9.3) with SMTP id h2ADpZpn019481 for ; Mon, 10 Mar 2003 15:51:35 +0200 Received: from share.pirmabanka.lv ([10.0.0.10]) by wall2.pirmabanka.lv; Mon, 10 Mar 2003 15:51:35 +0200 (EET) Received: from pc311x1.pirmabanka.lv (localhost.localdomain [127.0.0.1]) by pirmabanka.lv (8.11.6/8.11.6) with SMTP id h2ADpYr08269 for ; Mon, 10 Mar 2003 15:51:34 +0200 Received: (qmail 3813 invoked by uid 100); 10 Mar 2003 13:52:31 -0000 Date: Mon, 10 Mar 2003 15:52:31 +0200 From: "Victor Yegorov" To: "PostgreSQL SQL List" Subject: Optimizing view Message-ID: <20030310135231.GA3791@pirmabanka.lv> Mail-Followup-To: "PostgreSQL SQL List" Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="ZoaI/ZTpAVc4A5k6" Content-Disposition: inline User-Agent: Mutt/1.4i X-Archive-Number: 200303/238 X-Sequence-Number: 12278 --ZoaI/ZTpAVc4A5k6 Content-Type: multipart/mixed; boundary="jI8keyz6grp/JLjh" Content-Disposition: inline --jI8keyz6grp/JLjh Content-Type: text/plain; charset=koi8-r Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Hello. I'm running `PostgreSQL 7.2 on i586-pc-linux-gnu, compiled by GCC 2.95.3' and have some problems with queries on a view. I've got two tables: working one and archive and one view, I've attached th= e description. And here is my problem: queries on a view runs to much time. explain analyze select status, target, attempts from messages_all where mes= sage_id =3D 21823; NOTICE: QUERY PLAN: Subquery Scan messages_all (cost=3D2410.78..2887.07 rows=3D1270 width=3D21= 0) (actual time=3D5946.24..5948.41 rows=3D1 loops=3D1) -> Unique (cost=3D2410.78..2887.07 rows=3D1270 width=3D210) (actual tim= e=3D4567.78..5765.08 rows=3D12857 loops=3D1) -> Sort (cost=3D2410.78..2410.78 rows=3D12701 width=3D210) (actua= l time=3D4567.75..5043.40 rows=3D12857 loops=3D1) -> Append (cost=3D0.00..764.01 rows=3D12701 width=3D210) (a= ctual time=3D0.62..2346.45 rows=3D12857 loops=3D1) -> Subquery Scan *SELECT* 1 (cost=3D0.00..287.17 rows= =3D917 width=3D210) (actual time=3D0.61..229.99 rows=3D1073 loops=3D1) -> Seq Scan on messages (cost=3D0.00..287.17 ro= ws=3D917 width=3D210) (actual time=3D0.52..157.78 rows=3D1073 loops=3D1) -> Subquery Scan *SELECT* 2 (cost=3D0.00..476.84 rows= =3D11784 width=3D207) (actual time=3D3.13..2037.05 rows=3D11784 loops=3D1) -> Seq Scan on messages_archive (cost=3D0.00..4= 76.84 rows=3D11784 width=3D207) (actual time=3D3.04..1216.87 rows=3D11784 l= oops=3D1) Total runtime: 6101.58 msec EXPLAIN Too lo-o-ong for 13 000 tuples. When I UNION 2 queries from this tables, query is pretty fast: explain analyze select status, target, attempts from messages where message= _id =3D 21823 union select status, target, attempts from messages_archive w= here message_id =3D 21823; NOTICE: QUERY PLAN: Unique (cost=3D7.34..7.36 rows=3D1 width=3D19) (actual time=3D224.32..224.= 34 rows=3D1 loops=3D1) -> Sort (cost=3D7.34..7.34 rows=3D2 width=3D19) (actual time=3D224.31..= 224.31 rows=3D1 loops=3D1) -> Append (cost=3D0.00..7.33 rows=3D2 width=3D19) (actual time=3D= 128.29..205.15 rows=3D1 loops=3D1) -> Subquery Scan *SELECT* 1 (cost=3D0.00..4.24 rows=3D1 wid= th=3D19) (actual time=3D128.27..128.47 rows=3D1 loops=3D1) -> Index Scan using messages_primary on messages (cos= t=3D0.00..4.24 rows=3D1 width=3D19) (actual time=3D128.23..128.42 rows=3D1 = loops=3D1) -> Subquery Scan *SELECT* 2 (cost=3D0.00..3.10 rows=3D1 wid= th=3D19) (actual time=3D76.65..76.65 rows=3D0 loops=3D1) -> Index Scan using messages_archive_id on messages_ar= chive (cost=3D0.00..3.10 rows=3D1 width=3D19) (actual time=3D76.63..76.63 = rows=3D0 loops=3D1) Total runtime: 225.35 msec EXPLAIN I've read manual on REWRITE RULES, but I can't get, how to create RULE to k= eep the WHERE clause and separate one query into 2, and union them after. Any suggestions? --=20 Victor Yegorov --jI8keyz6grp/JLjh Content-Type: text/plain; charset=koi8-r Content-Disposition: attachment; filename="db-objects.sql" CREATE TABLE messages ( message_id INT4 NOT NULL, target VARCHAR(99), client_id INT NOT NULL, content VARCHAR(999), time_in TIMESTAMP NOT NULL, time_out TIMESTAMP, time_got TIMESTAMP, status INT NOT NULL, attempts INT NOT NULL, priority INT NOT NULL DEFAULT 0, notify INT, device INT, delay INT, validity INT NOT NULL DEFAULT -1, route_id INT, CONSTRAINT messages_primary PRIMARY KEY(message_id), CONSTRAINT messages_client_id FOREIGN KEY(client_id) REFERENCES clients(client_id), CONSTRAINT messages_status FOREIGN KEY(status) REFERENCES mstatus(status) ); CREATE TABLE messages_archive ( message_id INT4 NOT NULL, target VARCHAR(99), client_id INT NOT NULL, content VARCHAR(999), time_in TIMESTAMP NOT NULL, time_out TIMESTAMP, time_got TIMESTAMP, status INT NOT NULL, attempts INT NOT NULL, priority INT NOT NULL, notify INT, device INT, delay INT, validity INT NOT NULL, route_id INT ); CREATE VIEW messages_all AS SELECT * FROM messages UNION SELECT * FROM messages_archive; --jI8keyz6grp/JLjh-- --ZoaI/ZTpAVc4A5k6 Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE+bJif2RKGLEykUDYRAmEtAJ9v2/UjIfladVnPZR2H5yJZw5NbTwCfVQ7k 6dl5oQDfEyCW+GdCOmyj9pQ= =alRB -----END PGP SIGNATURE----- --ZoaI/ZTpAVc4A5k6--