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