473,287 Members | 1,926 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Slow performance with no apparent reason

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

Nov 12 '05 #1
2 1986
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
--
Martijn van Oosterhout <kl*****@svana.org> 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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/m3s6Y5Twig3Ge+YRAqv5AJ4lxUX6Hz1hr+YM0txMOvPGZhBXbw CfUbHZ
aUsIuCmq+jOnAL2+ykhVBrA=
=5CPY
-----END PGP SIGNATURE-----

Nov 12 '05 #2
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
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



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

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

--
Martijn van Oosterhout <kl*****@svana.org> 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


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE/m5QMY5Twig3Ge+YRAlGvAJsFovy27VJ47nkS9kITZnY+BH+OmQ Cggdqe
3LGYq5Dy3W8yvIB+z5vc084=
=gqtY
-----END PGP SIGNATURE-----

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Neil | last post by:
I've been discussing here a SQL 7 view which scrolls slowly when linked to an Access 2000 MDB. After trying various things, I've distilled it down to the following: when the linked view has a...
12
by: Neil | last post by:
I previously posted re. this, but thought I'd try again with a summary of facts. I have an Access 2000 MDB with a SQL Server 7 back end. There is a view that is linked to the database via ODBC...
5
by: Kurt Bauer | last post by:
I have an ASP group calendar application which pulls calendar data from Exchange via webdav into an XML string. I then loop the XML nodes to populate a collection of appointments. Finally I use...
4
by: Aaron McAlpine | last post by:
Our production system developed a problem over the Thanksgiving weekend. Wednesday evening... everything is fine. First thing Monday morning, we started receiving complaints about the system being...
6
by: Jrsmith | last post by:
I am developing a mdi application that will act as a front end to a database. I am finding that when i show any of the mdi child forms in my application it is noticeable that the form is shown...
8
by: Peter Rilling | last post by:
You know, this is my first day using VS.NET 2005 and, although it has some impressive new features, there are parts that they did not put together well. Does anyone else experience a periodical...
5
by: Rob R. Ainscough | last post by:
I have a moderately sized web application (30 pages, and 20 DLLs) that takes 10-20 minutes to "Build Solution" after I do a "Clean Solution" -- this is ONLY apparent after a "Clean Solution" I...
10
by: shsandeep | last post by:
DB2 V8.2 ICE Linux An application is loading around 6 million rows in a table in database A (Development environment). The same application is taking ages to load the same table in database B...
9
by: SAL | last post by:
I have an ASP.NET 2.0 app that takes about 17 seconds to load on first startup but then is very fast after that. As I understand it from some posts in June, this is caused by the loading of the App...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.