X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 154AED1B4E5 for ; Sun, 26 Oct 2003 07:44:36 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 87821-03 for ; Sun, 26 Oct 2003 03:44:06 -0400 (AST) Received: from svana.org (svana.org [203.20.62.76]) by svr1.postgresql.org (Postfix) with ESMTP id D312AD1B4F9 for ; Sun, 26 Oct 2003 03:44:03 -0400 (AST) Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian)) id 1ADfZ4-00053l-00; Sun, 26 Oct 2003 18:43:54 +1100 Date: Sun, 26 Oct 2003 18:43:54 +1100 From: Martijn van Oosterhout To: Yonatan Goraly Cc: pgsql-general@postgresql.org Subject: Re: Slow performance with no apparent reason Message-ID: <20031026074354.GB15100@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Yonatan Goraly , pgsql-general@postgresql.org References: <3F9AEA51.2070701@sbcglobal.net> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="SkvwRMAIpAhPCcCJ" Content-Disposition: inline In-Reply-To: <3F9AEA51.2070701@sbcglobal.net> User-Agent: Mutt/1.3.28i X-PGP-Key-ID: Length=1024; ID=0x0DC67BE6 X-PGP-Key-Fingerprint: 295F A899 A81A 156D B522 48A7 6394 F08A 0DC6 7BE6 X-PGP-Key-URL: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/1238 X-Sequence-Number: 51311 --SkvwRMAIpAhPCcCJ Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Please supply EXPLAIN ANALYZE output. On Sun, Oct 26, 2003 at 12:25:37AM +0300, Yonatan Goraly wrote: > I am in the process of adding PostgreSQL support for an application, in= =20 > addition to Oracle and MS SQL. > I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III boa= rd. >=20 > I have a query that generally looks like this: >=20 > SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=3Dt2.y AND t2.p=3D'string'= =20 > AND t2.q=3D1 >=20 > This query is strikingly slow (about 100 sec when both t1 and t2 has=20 > about 1,200 records, compare with less than 4 sec with MS SQL and Oracle) >=20 > The strange thing is that if I remove one of the last 2 conditions=20 > (doesn't matter which one), I get the same performance like with the=20 > other databases. > Since in this particular case both conditions ( t2.p=3D'string', t2.q=3D1= )=20 > are not required, I can't understand why having both turns the query so= =20 > slow. > A query on table t2 alone is fast with or without the 2 conditions. >=20 > I tired several alternatives, this one works pretty well: >=20 > SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=3Dt2.y AND > EXISTS ( > SELECT * FROM t2 t2a WHERE t2a.p=3D'string' AND t2a.q=3D1 AND= =20 > t2a.y=3Dt2.y ) >=20 > Since the first query is simpler than the second, it seems to me like a b= ug. >=20 > Please advise >=20 > Yonatan --=20 Martijn van Oosterhout http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato --SkvwRMAIpAhPCcCJ Content-Type: application/pgp-signature Content-Disposition: inline -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE/m3s6Y5Twig3Ge+YRAqv5AJ4lxUX6Hz1hr+YM0txMOvPGZhBXbwCfUbHZ aUsIuCmq+jOnAL2+ykhVBrA= =5CPY -----END PGP SIGNATURE----- --SkvwRMAIpAhPCcCJ--