X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 06118925007 for ; Sun, 1 Jun 2003 00:08:52 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 00556-05 for ; Sun, 1 Jun 2003 00:08:40 -0400 (EDT) Received: from simmts3-srv.bellnexxia.net (simmts3.bellnexxia.net [206.47.199.12]) by developer.postgresql.org (Postfix) with ESMTP id 6308E9257F9 for ; Sun, 1 Jun 2003 00:03:20 -0400 (EDT) Received: from [192.168.1.200] ([64.231.215.231]) by simmts3-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030601040311.ULJY3380.simmts3-srv.bellnexxia.net@[192.168.1.200]>; Sun, 1 Jun 2003 00:03:11 -0400 Subject: Re: are views typically any faster/slower than equivilent joins? From: Rod Taylor To: Brian Tarbox Cc: Postgresql Performance In-Reply-To: <001801c327e9$3c01a5b0$01000001@trouble> References: <001801c327e9$3c01a5b0$01000001@trouble> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-hn0s7XAM/1HaQLeYd4d/" Organization: Message-Id: <1054440158.11968.86.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 01 Jun 2003 00:02:39 -0400 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/2 X-Sequence-Number: 2086 --=-hn0s7XAM/1HaQLeYd4d/ Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Sat, 2003-05-31 at 22:55, Brian Tarbox wrote: > I am working with a highly normalized database. Most of my meaningful > queries involve joins from a primary table to 4-6 secondary tables. >=20 > Would my query performance be significantly faster/slower using a View as > opposed to a prepared query using join? There are some corner cases where a view would be slower than a standard query in 7.3 (bug fix / disabled optimization -- fixed right in 7.4), but generally you can assume it will be about the same speed. Some views such as unions will not be as fast as you would like, but thats a general issue with PostgreSQLs inability to throw away selects when it won't find results on one side of a union. CREATE VIEW sales AS SELECT * FROM sales_archive_2002 UNION ALL SELECT * FROM sales_current; SELECT * FROM sales WHERE timestamp =3D> CURRENT_TIMESTAMP - INTERVAL '1 day'; The above query would not be so quick. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-hn0s7XAM/1HaQLeYd4d/ Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (FreeBSD) iD8DBQA+2Xrd6DETLow6vwwRAh98AJ9RICX3wyq8VsWE0LMYK4FcBP7HZACfbK7+ LtWGpApA8NbyEUDzvWdPufQ= =o9jx -----END PGP SIGNATURE----- --=-hn0s7XAM/1HaQLeYd4d/--