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 2ABF5926018 for ; Sun, 1 Jun 2003 07:56:36 -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 93596-04 for ; Sun, 1 Jun 2003 07:56:25 -0400 (EDT) Received: from simmts8-srv.bellnexxia.net (simmts8.bellnexxia.net [206.47.199.166]) by developer.postgresql.org (Postfix) with ESMTP id 4EA28925C61 for ; Sun, 1 Jun 2003 07:56:25 -0400 (EDT) Received: from [192.168.1.200] ([64.231.215.231]) by simmts8-srv.bellnexxia.net (InterMail vM.5.01.05.32 201-253-122-126-132-20030307) with ESMTP id <20030601115624.ZAFZ20699.simmts8-srv.bellnexxia.net@[192.168.1.200]>; Sun, 1 Jun 2003 07:56:24 -0400 Subject: Re: are views typically any faster/slower than From: Rod Taylor To: Bruno Wolff III Cc: Brian Tarbox , Postgresql Performance In-Reply-To: <20030601054337.GD16259@wolff.to> References: <001801c327e9$3c01a5b0$01000001@trouble> <1054440158.11968.86.camel@jester> <20030601054337.GD16259@wolff.to> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-d7k9EV6Cwtzyzcp8DrLz" Organization: Message-Id: <1054468575.11968.95.camel@jester> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.4 Date: 01 Jun 2003 07:56:15 -0400 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/4 X-Sequence-Number: 2088 --=-d7k9EV6Cwtzyzcp8DrLz Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Sun, 2003-06-01 at 01:43, Bruno Wolff III wrote: > On Sun, Jun 01, 2003 at 00:02:39 -0400, > Rod Taylor wrote: > >=20 > > 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. > >=20 > > CREATE VIEW sales AS SELECT * FROM sales_archive_2002 UNION ALL SELECT * > > FROM sales_current; > >=20 > >=20 > > SELECT * FROM sales WHERE timestamp =3D> CURRENT_TIMESTAMP - INTERVAL '1 > > day'; > >=20 > > The above query would not be so quick. >=20 > I thought some work had been done on pushing where conditions down into > unions? If so the above wouldn't be too bad. It would still look at > the archive table, but it should return no rows relatively quickly > assuming an appropiate index exists. Certainly, if the index exists it won't be so bad (for any single archive table). It's when the index doesn't exist or there are several hundred archive tables then it starts to get a little worse. Anyway, anyone doing the above in PostgreSQL should probably be looking at partial indexes and merging the information back into a single table again. --=20 Rod Taylor PGP Key: http://www.rbt.ca/rbtpub.asc --=-d7k9EV6Cwtzyzcp8DrLz 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+2ene6DETLow6vwwRAo/QAJ9IvScAW8kCxHpgz/OLPYv3Z3vaqQCfTB2F /1F0FiAoZAvhoVvksUwaw48= =+XuI -----END PGP SIGNATURE----- --=-d7k9EV6Cwtzyzcp8DrLz--