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 D1728D1B548; Sun, 26 Oct 2003 07:26:57 +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 79605-04; Sun, 26 Oct 2003 03:26:28 -0400 (AST) Received: from pimout4-ext.prodigy.net (pimout4-ext.prodigy.net [207.115.63.103]) by svr1.postgresql.org (Postfix) with ESMTP id 7C3E4D1B545; Sun, 26 Oct 2003 03:26:26 -0400 (AST) Received: from sbcglobal.net (adsl-64-169-94-134.dsl.snfc21.pacbell.net [64.169.94.134]) by pimout4-ext.prodigy.net (8.12.9/8.12.3) with ESMTP id h9Q7QQl0145274; Sun, 26 Oct 2003 02:26:27 -0500 Message-ID: <3F9AEA51.2070701@sbcglobal.net> Date: Sun, 26 Oct 2003 00:25:37 +0300 From: Yonatan Goraly User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4.1) Gecko/20030917 X-Accept-Language: en-us, en, ja MIME-Version: 1.0 To: pgsql-general@postgresql.org, pgsql-performance@postgresql.org Subject: Slow performance with no apparent reason Content-Type: multipart/alternative; boundary="------------070309060100090007060705" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/1237 X-Sequence-Number: 51310 This is a multi-part message in MIME format. --------------070309060100090007060705 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I am in the process of adding PostgreSQL support for an application, in addition to Oracle and MS SQL. I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board. I have a query that generally looks like this: SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND t2.p='string' AND t2.q=1 This query is strikingly slow (about 100 sec when both t1 and t2 has about 1,200 records, compare with less than 4 sec with MS SQL and Oracle) The strange thing is that if I remove one of the last 2 conditions (doesn't matter which one), I get the same performance like with the other databases. Since in this particular case both conditions ( t2.p='string', t2.q=1) are not required, I can't understand why having both turns the query so slow. A query on table t2 alone is fast with or without the 2 conditions. I tired several alternatives, this one works pretty well: SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND EXISTS ( SELECT * FROM t2 t2a WHERE t2a.p='string' AND t2a.q=1 AND t2a.y=t2.y ) Since the first query is simpler than the second, it seems to me like a bug. Please advise Yonatan --------------070309060100090007060705 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I am in the process of  adding PostgreSQL support for an application, in addition to Oracle and MS SQL.
I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III board.

I have a query that generally looks like this:

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND t2.p='string' AND t2.q=1

This query is strikingly slow (about 100 sec when both t1 and t2 has about 1,200 records, compare with less than 4 sec with MS SQL and Oracle)

The strange thing is that if I remove one of the last 2 conditions (doesn't matter which one), I get the same performance like with the other databases.
Since in this particular case both conditions ( t2.p='string', t2.q=1) are not required, I can't understand why having both turns the query so slow.
A query on table t2 alone is fast with or without the 2 conditions.

I tired several alternatives, this one works pretty well:

SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=t2.y AND
     EXISTS (
             SELECT * FROM t2 t2a WHERE t2a.p='string' AND t2a.q=1 AND t2a.y=t2.y )

Since the first query is simpler than the second, it seems to me like a bug.

Please advise

Yonatan
--------------070309060100090007060705--