From ygoraly@sbcglobal.net Sun Oct 26 07:26:57 2003 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-- From ygoraly@sbcglobal.net Sun Oct 26 08:27:46 2003 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 F035FD1B4E5 for ; Sun, 26 Oct 2003 08:27:46 +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 95995-01 for ; Sun, 26 Oct 2003 04:27:16 -0400 (AST) Received: from pimout4-ext.prodigy.net (pimout4-ext.prodigy.net [207.115.63.103]) by svr1.postgresql.org (Postfix) with ESMTP id 958C5D1B529 for ; Sun, 26 Oct 2003 04:27:12 -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 h9Q8RBl0092778; Sun, 26 Oct 2003 03:27:12 -0500 Message-ID: <3F9AF88E.3040101@sbcglobal.net> Date: Sun, 26 Oct 2003 01:26:22 +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: Martijn van Oosterhout Cc: pgsql-general@postgresql.org Subject: Re: Slow performance with no apparent reason References: <3F9AEA51.2070701@sbcglobal.net> <20031026074354.GB15100@svana.org> In-Reply-To: <20031026074354.GB15100@svana.org> Content-Type: multipart/mixed; boundary="------------050205040909090901010203" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/1239 X-Sequence-Number: 51312 This is a multi-part message in MIME format. --------------050205040909090901010203 Content-Type: multipart/alternative; boundary="------------090009040505030800080503" --------------090009040505030800080503 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit I guess my first message was not accurate, since t1 is a view, that includes t2. Attached are the real queries with their corresponding plans, the first one takes 10.8 sec to execute, the second one takes 0.6 sec. To simplify, I expanded the view, so the attached query refers to tables only. Martijn van Oosterhout wrote: >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 >>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 >> >> > > > --------------090009040505030800080503 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit I guess my first message was not accurate, since t1 is a view, that includes t2.

Attached are the real queries with their corresponding plans, the first one takes 10.8 sec to execute, the second one takes 0.6 sec.

To simplify, I expanded the view, so the attached query refers to tables only.

Martijn van Oosterhout wrote:
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 
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
    

  
--------------090009040505030800080503-- --------------050205040909090901010203 Content-Type: text/plain; name="Queries.txt" Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="Queries.txt" ------------------------------------------------------------------------------------------------------------ slow query(10 sec): select ent.ID,ent.TYPE,ent.STATUS,ent.NAME from (select e.ID, e.TYPE, e.STATUS, e.NAME from ENT_PROJECT e, (select h.*, CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME || CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME || CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME || CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME || CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME || CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END END END END as PATH from COMN_ATTR_HIERARCH h join ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID ) path where e.STATUS!=cast(-1 as numeric) and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier where hier.NODE_ID=ent.ID and hier.HIERARCHY_ID='IMPLEMENTATION' and hier.DOMAIN=1 ------------------------------------------------------------------------------------------------------------ QUERY PLAN Nested Loop (cost=1808.05..1955.27 rows=14 width=660) Join Filter: ("outer".id = "inner".node_id) -> Nested Loop (cost=0.00..10.82 rows=1 width=244) -> Index Scan using idx_hierarch_hierarch_id on comn_attr_hierarch hier (cost=0.00..5.98 rows=1 width=32) Index Cond: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND ("domain" = 1::numeric)) -> Index Scan using pk_ent_project on ent_project e (cost=0.00..4.83 rows=1 width=212) Index Cond: ("outer".node_id = e.id) Filter: (status <> -1::numeric) -> Materialize (cost=1910.33..1910.33 rows=2730 width=416) -> Merge Join (cost=1808.05..1910.33 rows=2730 width=416) Merge Cond: ("outer".id = "inner".folder_id_6) -> Index Scan using pk_ent_folder on ent_folder f6 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1808.05..1814.88 rows=2730 width=384) Sort Key: h.folder_id_6 -> Merge Join (cost=1275.45..1377.73 rows=2730 width=384) Merge Cond: ("outer".id = "inner".folder_id_5) -> Index Scan using pk_ent_folder on ent_folder f5 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1275.45..1282.28 rows=2730 width=352) Sort Key: h.folder_id_5 -> Merge Join (cost=1017.37..1119.64 rows=2730 width=352) Merge Cond: ("outer".id = "inner".folder_id_4) -> Index Scan using pk_ent_folder on ent_folder f4 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1017.37..1024.19 rows=2730 width=320) Sort Key: h.folder_id_4 -> Merge Join (cost=759.28..861.56 rows=2730 width=320) Merge Cond: ("outer".id = "inner".folder_id_3) -> Index Scan using pk_ent_folder on ent_folder f3 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=759.28..766.11 rows=2730 width=288) Sort Key: h.folder_id_3 -> Merge Join (cost=501.20..603.47 rows=2730 width=288) Merge Cond: ("outer".id = "inner".folder_id_2) -> Index Scan using pk_ent_folder on ent_folder f2 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=501.20..508.02 rows=2730 width=256) Sort Key: h.folder_id_2 -> Merge Join (cost=243.11..345.39 rows=2730 width=256) Merge Cond: ("outer".id = "inner".folder_id_1) -> Index Scan using pk_ent_folder on ent_folder f1 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=243.11..249.94 rows=2730 width=224) Sort Key: h.folder_id_1 -> Seq Scan on comn_attr_hierarch h (cost=0.00..87.30 rows=2730 width=224) ------------------------------------------------------------------------------------------------------------ Fast query (.6 sec): select ent.ID,ent.TYPE,ent.STATUS,ent.NAME from (select e.ID, e.TYPE, e.STATUS, e.NAME from ENT_PROJECT e, (select h.*, CASE WHEN f1.ID=-1 THEN '' ELSE f1.NAME || CASE WHEN f2.ID=-1 THEN '' ELSE ' > ' || f2.NAME || CASE WHEN f3.ID=-1 THEN '' ELSE ' > ' || f3.NAME || CASE WHEN f4.ID=-1 THEN '' ELSE ' > ' || f4.NAME || CASE WHEN f5.ID=-1 THEN '' ELSE ' > ' || f5.NAME || CASE WHEN f6.ID=-1 THEN '' ELSE ' > ' || f6.NAME END END END END END END as PATH from COMN_ATTR_HIERARCH h join ENT_FOLDER f1 on h.FOLDER_ID_1=f1.ID join ENT_FOLDER f2 on h.FOLDER_ID_2=f2.ID join ENT_FOLDER f3 on h.FOLDER_ID_3=f3.ID join ENT_FOLDER f4 on h.FOLDER_ID_4=f4.ID join ENT_FOLDER f5 on h.FOLDER_ID_5=f5.ID join ENT_FOLDER f6 on h.FOLDER_ID_6=f6.ID ) path where e.STATUS!=cast(-1 as numeric) and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERARCH hier where hier.NODE_ID=ent.ID and exists( select * from COMN_ATTR_HIERARCH h2 where h2.HIERARCHY_ID='IMPLEMENTATION' and h2.DOMAIN=1 and h2.NODE_ID=hier.NODE_ID and h2.HIERARCHY_ID=hier.HIERARCHY_ID and h2.DOMAIN=hier.DOMAIN) ------------------------------------------------------------------------------------------------------------ QUERY PLAN Merge Join (cost=16145.60..16289.84 rows=18539 width=660) Merge Cond: ("outer".id = "inner".node_id) -> Merge Join (cost=13782.29..13863.08 rows=1358 width=244) Merge Cond: ("outer".id = "inner".node_id) -> Index Scan using pk_ent_project on ent_project e (cost=0.00..54.50 rows=995 width=212) Filter: (status <> -1::numeric) -> Sort (cost=13782.29..13785.70 rows=1365 width=32) Sort Key: hier.node_id -> Seq Scan on comn_attr_hierarch hier (cost=0.00..13711.21 rows=1365 width=32) Filter: (subplan) SubPlan -> Index Scan using pk_comn_attr_hierarch on comn_attr_hierarch h2 (cost=0.00..4.99 rows=1 width=316) Index Cond: (("domain" = 1::numeric) AND ("domain" = $2) AND (node_id = $0)) Filter: ((hierarchy_id = 'IMPLEMENTATION'::bpchar) AND (hierarchy_id = $1)) -> Sort (cost=2363.32..2370.14 rows=2730 width=416) Sort Key: h.node_id -> Merge Join (cost=1808.05..1910.33 rows=2730 width=416) Merge Cond: ("outer".id = "inner".folder_id_6) -> Index Scan using pk_ent_folder on ent_folder f6 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1808.05..1814.88 rows=2730 width=384) Sort Key: h.folder_id_6 -> Merge Join (cost=1275.45..1377.73 rows=2730 width=384) Merge Cond: ("outer".id = "inner".folder_id_5) -> Index Scan using pk_ent_folder on ent_folder f5 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1275.45..1282.28 rows=2730 width=352) Sort Key: h.folder_id_5 -> Merge Join (cost=1017.37..1119.64 rows=2730 width=352) Merge Cond: ("outer".id = "inner".folder_id_4) -> Index Scan using pk_ent_folder on ent_folder f4 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=1017.37..1024.19 rows=2730 width=320) Sort Key: h.folder_id_4 -> Merge Join (cost=759.28..861.56 rows=2730 width=320) Merge Cond: ("outer".id = "inner".folder_id_3) -> Index Scan using pk_ent_folder on ent_folder f3 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=759.28..766.11 rows=2730 width=288) Sort Key: h.folder_id_3 -> Merge Join (cost=501.20..603.47 rows=2730 width=288) Merge Cond: ("outer".id = "inner".folder_id_2) -> Index Scan using pk_ent_folder on ent_folder f2 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=501.20..508.02 rows=2730 width=256) Sort Key: h.folder_id_2 -> Merge Join (cost=243.11..345.39 rows=2730 width=256) Merge Cond: ("outer".id = "inner".folder_id_1) -> Index Scan using pk_ent_folder on ent_folder f1 (cost=0.00..52.00 rows=1000 width=32) -> Sort (cost=243.11..249.94 rows=2730 width=224) Sort Key: h.folder_id_1 -> Seq Scan on comn_attr_hierarch h (cost=0.00..87.30 rows=2730 width=224) ------------------------------------------------------------------------------------------------------------ --------------050205040909090901010203-- From kleptog@svana.org Sun Oct 26 07:44:36 2003 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-- From kleptog@svana.org Sun Oct 26 09:30:48 2003 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 A01A0D1B543 for ; Sun, 26 Oct 2003 09:30:48 +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 03656-02 for ; Sun, 26 Oct 2003 05:30:19 -0400 (AST) Received: from svana.org (svana.org [203.20.62.76]) by svr1.postgresql.org (Postfix) with ESMTP id DF0C2D1B51B for ; Sun, 26 Oct 2003 05:30:15 -0400 (AST) Received: from kleptog by svana.org with local (Exim 3.35 #1 (Debian)) id 1ADhDY-0005Iu-00; Sun, 26 Oct 2003 20:29:48 +1100 Date: Sun, 26 Oct 2003 20:29:48 +1100 From: Martijn van Oosterhout To: Yonatan Goraly Cc: pgsql-general@postgresql.org Subject: Re: Slow performance with no apparent reason Message-ID: <20031026092948.GC15100@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Yonatan Goraly , pgsql-general@postgresql.org References: <3F9AEA51.2070701@sbcglobal.net> <20031026074354.GB15100@svana.org> <3F9AF88E.3040101@sbcglobal.net> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="KDt/GgjP6HVcx58l" Content-Disposition: inline In-Reply-To: <3F9AF88E.3040101@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/1240 X-Sequence-Number: 51313 --KDt/GgjP6HVcx58l Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Ok, those figures look like you've mever run ANALYZE on that database at all, given you keep getting the default values. EXPLAIN ANALYZE would have given the actual number of matching rows. Given that, the plans are probably extremely suboptimal. Also, do you have (unique) indexes on the columns that need it. So the EXPLAIN ANALYZE output after running ANALYZE over your database would be the next step. Hope this helps, On Sun, Oct 26, 2003 at 01:26:22AM +0300, Yonatan Goraly wrote: > I guess my first message was not accurate, since t1 is a view, that=20 > includes t2. >=20 > Attached are the real queries with their corresponding plans, the first= =20 > one takes 10.8 sec to execute, the second one takes 0.6 sec. >=20 > To simplify, I expanded the view, so the attached query refers to tables= =20 > only. >=20 > Martijn van Oosterhout wrote: >=20 > >Please supply EXPLAIN ANALYZE output. > > > >On Sun, Oct 26, 2003 at 12:25:37AM +0300, Yonatan Goraly wrote: > >=20 > > > >>I am in the process of adding PostgreSQL support for an application, i= n=20 > >>addition to Oracle and MS SQL. > >>I am using PostgreSQL version 7.3.2, Red Hat 9.0 on Intel Pentium III= =20 > >>board. > >> > >>I have a query that generally looks like this: > >> > >>SELECT t1.col1, t2.col1 FROM t1, t2 WHERE t1.x=3Dt2.y AND t2.p=3D'strin= g'=20 > >>AND t2.q=3D1 > >> > >>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 Oracl= e) > >> > >>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. > >> > >>I tired several alternatives, this one works pretty well: > >> > >>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 AN= D=20 > >>t2a.y=3Dt2.y ) > >> > >>Since the first query is simpler than the second, it seems to me like a= =20 > >>bug. > >> > >>Please advise > >> > >>Yonatan > >>=20=20=20 > >> > > > >=20 > > > -------------------------------------------------------------------------= ----------------------------------- > slow query(10 sec): >=20 > select ent.ID,ent.TYPE,ent.STATUS,ent.NAME > from (select > e.ID, e.TYPE, e.STATUS, e.NAME > from > ENT_PROJECT e, > (select h.*, > CASE WHEN f1.ID=3D-1 THEN '' ELSE f1.NAME || > CASE WHEN f2.ID=3D-1 THEN '' ELSE ' > ' || f2.NAME || > CASE WHEN f3.ID=3D-1 THEN '' ELSE ' > ' || f3.NAME || > CASE WHEN f4.ID=3D-1 THEN '' ELSE ' > ' || f4.NAME || > CASE WHEN f5.ID=3D-1 THEN '' ELSE ' > ' || f5.NAME || > CASE WHEN f6.ID=3D-1 THEN '' ELSE ' > ' || f6.NAME END= END END END END END as PATH > from COMN_ATTR_HIERARCH h > join ENT_FOLDER f1 on h.FOLDER_ID_1=3Df1.ID > join ENT_FOLDER f2 on h.FOLDER_ID_2=3Df2.ID > join ENT_FOLDER f3 on h.FOLDER_ID_3=3Df3.ID > join ENT_FOLDER f4 on h.FOLDER_ID_4=3Df4.ID > join ENT_FOLDER f5 on h.FOLDER_ID_5=3Df5.ID > join ENT_FOLDER f6 on h.FOLDER_ID_6=3Df6.ID > ) path > where e.STATUS!=3Dcast(-1 as numeric) > and e.ID =3D path.NODE_ID) ent , COMN_ATTR_HIERARCH hier > where hier.NODE_ID=3Dent.ID and hier.HIERARCHY_ID=3D'IMPLEMENTATION' = and hier.DOMAIN=3D1 >=20 >=20 > -------------------------------------------------------------------------= ----------------------------------- > QUERY PLAN > Nested Loop (cost=3D1808.05..1955.27 rows=3D14 width=3D660) > Join Filter: ("outer".id =3D "inner".node_id) > -> Nested Loop (cost=3D0.00..10.82 rows=3D1 width=3D244) > -> Index Scan using idx_hierarch_hierarch_id on comn_attr_hierar= ch hier (cost=3D0.00..5.98 rows=3D1 width=3D32) > Index Cond: ((hierarchy_id =3D 'IMPLEMENTATION'::bpchar) AN= D ("domain" =3D 1::numeric)) > -> Index Scan using pk_ent_project on ent_project e (cost=3D0.0= 0..4.83 rows=3D1 width=3D212) > Index Cond: ("outer".node_id =3D e.id) > Filter: (status <> -1::numeric) > -> Materialize (cost=3D1910.33..1910.33 rows=3D2730 width=3D416) > -> Merge Join (cost=3D1808.05..1910.33 rows=3D2730 width=3D416) > Merge Cond: ("outer".id =3D "inner".folder_id_6) > -> Index Scan using pk_ent_folder on ent_folder f6 (cost= =3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D1808.05..1814.88 rows=3D2730 width=3D384) > Sort Key: h.folder_id_6 > -> Merge Join (cost=3D1275.45..1377.73 rows=3D2730 = width=3D384) > Merge Cond: ("outer".id =3D "inner".folder_id_5) > -> Index Scan using pk_ent_folder on ent_folde= r f5 (cost=3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D1275.45..1282.28 rows=3D2730 = width=3D352) > Sort Key: h.folder_id_5 > -> Merge Join (cost=3D1017.37..1119.64 = rows=3D2730 width=3D352) > Merge Cond: ("outer".id =3D "inner"= .folder_id_4) > -> Index Scan using pk_ent_folder = on ent_folder f4 (cost=3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D1017.37..1024.19 = rows=3D2730 width=3D320) > Sort Key: h.folder_id_4 > -> Merge Join (cost=3D759.2= 8..861.56 rows=3D2730 width=3D320) > Merge Cond: ("outer".id= =3D "inner".folder_id_3) > -> Index Scan using pk= _ent_folder on ent_folder f3 (cost=3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D759.2= 8..766.11 rows=3D2730 width=3D288) > Sort Key: h.folde= r_id_3 > -> Merge Join (= cost=3D501.20..603.47 rows=3D2730 width=3D288) > Merge Cond:= ("outer".id =3D "inner".folder_id_2) > -> Index S= can using pk_ent_folder on ent_folder f2 (cost=3D0.00..52.00 rows=3D1000 w= idth=3D32) > -> Sort (= cost=3D501.20..508.02 rows=3D2730 width=3D256) > Sort = Key: h.folder_id_2 > -> M= erge Join (cost=3D243.11..345.39 rows=3D2730 width=3D256) > = Merge Cond: ("outer".id =3D "inner".folder_id_1) > = -> Index Scan using pk_ent_folder on ent_folder f1 (cost=3D0.00..52.00 r= ows=3D1000 width=3D32) > = -> Sort (cost=3D243.11..249.94 rows=3D2730 width=3D224) > = Sort Key: h.folder_id_1 > = -> Seq Scan on comn_attr_hierarch h (cost=3D0.00..87.30 rows=3D273= 0 width=3D224) >=20 >=20 > -------------------------------------------------------------------------= ----------------------------------- > Fast query (.6 sec): >=20 > select ent.ID,ent.TYPE,ent.STATUS,ent.NAME > from (select > e.ID, e.TYPE, e.STATUS, e.NAME > from > ENT_PROJECT e, > (select h.*, > CASE WHEN f1.ID=3D-1 THEN '' ELSE f1.NAME || > CASE WHEN f2.ID=3D-1 THEN '' ELSE ' > ' || f2.NAME || > CASE WHEN f3.ID=3D-1 THEN '' ELSE ' > ' || f3.NAME || > CASE WHEN f4.ID=3D-1 THEN '' ELSE ' > ' || f4.NAME || > CASE WHEN f5.ID=3D-1 THEN '' ELSE ' > ' || f5.NAME || > CASE WHEN f6.ID=3D-1 THEN '' ELSE ' > ' || f6.NAME END= END END END END END as PATH > from COMN_ATTR_HIERARCH h > join ENT_FOLDER f1 on h.FOLDER_ID_1=3Df1.ID > join ENT_FOLDER f2 on h.FOLDER_ID_2=3Df2.ID > join ENT_FOLDER f3 on h.FOLDER_ID_3=3Df3.ID > join ENT_FOLDER f4 on h.FOLDER_ID_4=3Df4.ID > join ENT_FOLDER f5 on h.FOLDER_ID_5=3Df5.ID > join ENT_FOLDER f6 on h.FOLDER_ID_6=3Df6.ID > ) path > where e.STATUS!=3Dcast(-1 as numeric) > and e.ID =3D path.NODE_ID) ent , COMN_ATTR_HIERARCH hier > where hier.NODE_ID=3Dent.ID and exists( > select * from COMN_ATTR_HIERARCH h2 where h2.HIERARCHY_ID=3D'IMPLEMENT= ATION' and h2.DOMAIN=3D1 and h2.NODE_ID=3Dhier.NODE_ID > and h2.HIERARCHY_ID=3Dhier.HIERARCHY_ID and h2.DOMAIN=3Dhier.DOMAIN) >=20 >=20 > -------------------------------------------------------------------------= ----------------------------------- > QUERY PLAN > Merge Join (cost=3D16145.60..16289.84 rows=3D18539 width=3D660) > Merge Cond: ("outer".id =3D "inner".node_id) > -> Merge Join (cost=3D13782.29..13863.08 rows=3D1358 width=3D244) > Merge Cond: ("outer".id =3D "inner".node_id) > -> Index Scan using pk_ent_project on ent_project e (cost=3D0.0= 0..54.50 rows=3D995 width=3D212) > Filter: (status <> -1::numeric) > -> Sort (cost=3D13782.29..13785.70 rows=3D1365 width=3D32) > Sort Key: hier.node_id > -> Seq Scan on comn_attr_hierarch hier (cost=3D0.00..1371= 1.21 rows=3D1365 width=3D32) > Filter: (subplan) > SubPlan > -> Index Scan using pk_comn_attr_hierarch on comn_= attr_hierarch h2 (cost=3D0.00..4.99 rows=3D1 width=3D316) > Index Cond: (("domain" =3D 1::numeric) AND ("= domain" =3D $2) AND (node_id =3D $0)) > Filter: ((hierarchy_id =3D 'IMPLEMENTATION'::= bpchar) AND (hierarchy_id =3D $1)) > -> Sort (cost=3D2363.32..2370.14 rows=3D2730 width=3D416) > Sort Key: h.node_id > -> Merge Join (cost=3D1808.05..1910.33 rows=3D2730 width=3D416) > Merge Cond: ("outer".id =3D "inner".folder_id_6) > -> Index Scan using pk_ent_folder on ent_folder f6 (cost= =3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D1808.05..1814.88 rows=3D2730 width=3D384) > Sort Key: h.folder_id_6 > -> Merge Join (cost=3D1275.45..1377.73 rows=3D2730 = width=3D384) > Merge Cond: ("outer".id =3D "inner".folder_id_5) > -> Index Scan using pk_ent_folder on ent_folde= r f5 (cost=3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D1275.45..1282.28 rows=3D2730 = width=3D352) > Sort Key: h.folder_id_5 > -> Merge Join (cost=3D1017.37..1119.64 = rows=3D2730 width=3D352) > Merge Cond: ("outer".id =3D "inner"= .folder_id_4) > -> Index Scan using pk_ent_folder = on ent_folder f4 (cost=3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D1017.37..1024.19 = rows=3D2730 width=3D320) > Sort Key: h.folder_id_4 > -> Merge Join (cost=3D759.2= 8..861.56 rows=3D2730 width=3D320) > Merge Cond: ("outer".id= =3D "inner".folder_id_3) > -> Index Scan using pk= _ent_folder on ent_folder f3 (cost=3D0.00..52.00 rows=3D1000 width=3D32) > -> Sort (cost=3D759.2= 8..766.11 rows=3D2730 width=3D288) > Sort Key: h.folde= r_id_3 > -> Merge Join (= cost=3D501.20..603.47 rows=3D2730 width=3D288) > Merge Cond:= ("outer".id =3D "inner".folder_id_2) > -> Index S= can using pk_ent_folder on ent_folder f2 (cost=3D0.00..52.00 rows=3D1000 w= idth=3D32) > -> Sort (= cost=3D501.20..508.02 rows=3D2730 width=3D256) > Sort = Key: h.folder_id_2 > -> M= erge Join (cost=3D243.11..345.39 rows=3D2730 width=3D256) > = Merge Cond: ("outer".id =3D "inner".folder_id_1) > = -> Index Scan using pk_ent_folder on ent_folder f1 (cost=3D0.00..52.00 r= ows=3D1000 width=3D32) > = -> Sort (cost=3D243.11..249.94 rows=3D2730 width=3D224) > = Sort Key: h.folder_id_1 > = -> Seq Scan on comn_attr_hierarch h (cost=3D0.00..87.30 rows=3D273= 0 width=3D224) >=20 > -------------------------------------------------------------------------= ----------------------------------- >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match --=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 --KDt/GgjP6HVcx58l 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/m5QMY5Twig3Ge+YRAlGvAJsFovy27VJ47nkS9kITZnY+BH+OmQCggdqe 3LGYq5Dy3W8yvIB+z5vc084= =gqtY -----END PGP SIGNATURE----- --KDt/GgjP6HVcx58l-- From ygoraly@sbcglobal.net Sun Oct 26 23:29:51 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 09DC5D1B4F9 for ; Sun, 26 Oct 2003 23:29:51 +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 24550-02 for ; Sun, 26 Oct 2003 19:29:22 -0400 (AST) Received: from pimout4-ext.prodigy.net (pimout4-ext.prodigy.net [207.115.63.103]) by svr1.postgresql.org (Postfix) with ESMTP id 49A6FD1B4EF for ; Sun, 26 Oct 2003 19:29:19 -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 h9QNTDl0106828; Sun, 26 Oct 2003 18:29:21 -0500 Message-ID: <3F9BCBF5.2090506@sbcglobal.net> Date: Sun, 26 Oct 2003 15:28:21 +0200 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: Hannu Krosing Cc: pgsql-performance@postgresql.org Subject: Re: Slow performance with no apparent reason References: <3F9AEA51.2070701@sbcglobal.net> <1067179095.2604.3.camel@fuji.krosing.net> In-Reply-To: <1067179095.2604.3.camel@fuji.krosing.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/657 X-Sequence-Number: 4405 I run ANALYZE and the problem resolved Thanks >Yonatan Goraly kirjutas P, 26.10.2003 kell 00:25: > > >>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) >> >> > >always send results of EXPLAIN ANALYZE if you ask for help on [PERFORM] > >knowing which indexes you have would also help. > >and you should have run ANALYZE too. > >----------------- >Hannu > > > > From hannu@tm.ee Sun Oct 26 14:40:51 2003 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 37B31D1B548; Sun, 26 Oct 2003 14:40:51 +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 36900-09; Sun, 26 Oct 2003 10:40:24 -0400 (AST) Received: from fuji.krosing.net (silmet.estpak.ee [194.126.97.78]) by svr1.postgresql.org (Postfix) with ESMTP id 64135D1B554; Sun, 26 Oct 2003 10:40:19 -0400 (AST) Received: from fuji.krosing.net (localhost.localdomain [127.0.0.1]) by fuji.krosing.net (8.12.8/8.12.8) with ESMTP id h9QEcG5r002703; Sun, 26 Oct 2003 16:38:17 +0200 Received: (from hannu@localhost) by fuji.krosing.net (8.12.8/8.12.8/Submit) id h9QEcGEp002701; Sun, 26 Oct 2003 16:38:16 +0200 X-Authentication-Warning: fuji.krosing.net: hannu set sender to hannu@tm.ee using -f Subject: Re: [PERFORM] Slow performance with no apparent reason From: Hannu Krosing To: Yonatan Goraly Cc: pgsql-general@postgresql.org, pgsql-performance@postgresql.org In-Reply-To: <3F9AEA51.2070701@sbcglobal.net> References: <3F9AEA51.2070701@sbcglobal.net> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1067179095.2604.3.camel@fuji.krosing.net> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.5 Date: Sun, 26 Oct 2003 16:38:16 +0200 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/1247 X-Sequence-Number: 51320 Yonatan Goraly kirjutas P, 26.10.2003 kell 00:25: > 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) always send results of EXPLAIN ANALYZE if you ask for help on [PERFORM] knowing which indexes you have would also help. and you should have run ANALYZE too. ----------------- Hannu