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--