pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Yonatan Goraly <ygoraly@sbcglobal.net>
To: Martijn van Oosterhout <kleptog@svana.org>
Cc: pgsql-general@postgresql.org
Subject: Re: Slow performance with no apparent reason
Date: Sun, 26 Oct 2003 01:26:22 +0300
Message-ID: <3F9AF88E.3040101@sbcglobal.net> (raw)
In-Reply-To: <20031026074354.GB15100@svana.org>
References: <3F9AEA51.2070701@sbcglobal.net>
	<20031026074354.GB15100@svana.org>

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

Attachments:

Queries.txt (text/plain, 10.6K) download ------------------------------------------------------------------------------------------------------------ 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) ------------------------------------------------------------------------------------------------------------

view thread (6+ messages)  latest in thread

Message-ID: <3F9AF88E.3040101@sbcglobal.net>
Permalink:  ../3F9AF88E.3040101@sbcglobal.net/
Also on:    postgresql.org/message-id/3F9AF88E.3040101@sbcglobal.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ygoraly@sbcglobal.net, kleptog@svana.org, pgsql-general@postgresql.org
  Subject: Re: Slow performance with no apparent reason
  In-Reply-To: <3F9AF88E.3040101@sbcglobal.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox