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 2 2017
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+Y RAqv5AJ4lxUX6Hz 1hr+YM0txMOvPGZ hBXbwCfUbHZ
aUsIuCmq+jOnAL2 +ykhVBrA=
=5CPY
-----END PGP SIGNATURE-----
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_HIERA RCH h join ENT_FOLDER f1 on h.FOLDER_ID_1=f 1.ID join ENT_FOLDER f2 on h.FOLDER_ID_2=f 2.ID join ENT_FOLDER f3 on h.FOLDER_ID_3=f 3.ID join ENT_FOLDER f4 on h.FOLDER_ID_4=f 4.ID join ENT_FOLDER f5 on h.FOLDER_ID_5=f 5.ID join ENT_FOLDER f6 on h.FOLDER_ID_6=f 6.ID ) path where e.STATUS!=cast(-1 as numeric) and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERA RCH hier where hier.NODE_ID=en t.ID and hier.HIERARCHY_ ID='IMPLEMENTAT ION' 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_hi erarch_id on comn_attr_hiera rch hier (cost=0.00..5.9 8 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.8 3 rows=1 width=212) Index Cond: ("outer".node_i d = 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..8 61.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..7 66.11 rows=2730 width=288) Sort Key: h.folder_id_3 -> Merge Join (cost=501.20..6 03.47 rows=2730 width=288) Merge Cond:("outer".i d = "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..5 08.02 rows=2730 width=256) Sort Key: h.folder_id_2 -> Merge Join (cost=243.11..3 45.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..2 49.94 rows=2730 width=224) Sort Key: h.folder_id_1 -> Seq Scan on comn_attr_hiera rch 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_HIERA RCH h join ENT_FOLDER f1 on h.FOLDER_ID_1=f 1.ID join ENT_FOLDER f2 on h.FOLDER_ID_2=f 2.ID join ENT_FOLDER f3 on h.FOLDER_ID_3=f 3.ID join ENT_FOLDER f4 on h.FOLDER_ID_4=f 4.ID join ENT_FOLDER f5 on h.FOLDER_ID_5=f 5.ID join ENT_FOLDER f6 on h.FOLDER_ID_6=f 6.ID ) path where e.STATUS!=cast(-1 as numeric) and e.ID = path.NODE_ID) ent , COMN_ATTR_HIERA RCH hier where hier.NODE_ID=en t.ID and exists( select * from COMN_ATTR_HIERA RCH h2 where h2.HIERARCHY_ID ='IMPLEMENTATIO N' 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_hiera rch hier (cost=0.00..137 11.21 rows=1365 width=32) Filter: (subplan) SubPlan -> Index Scan using pk_comn_attr_hi erarch on comn_attr_hiera rch h2 (cost=0.00..4.9 9 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..8 61.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..7 66.11 rows=2730 width=288) Sort Key: h.folder_id_3 -> Merge Join (cost=501.20..6 03.47 rows=2730 width=288) Merge Cond:("outer".i d = "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..5 08.02 rows=2730 width=256) Sort Key: h.folder_id_2 -> Merge Join (cost=243.11..3 45.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..2 49.94 rows=2730 width=224) Sort Key: h.folder_id_1 -> Seq Scan on comn_attr_hiera rch 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+Y RAlGvAJsFovy27V J47nkS9kITZnY+B H+OmQCggdqe
3LGYq5Dy3W8yvIB +z5vc084=
=gqtY
-----END PGP SIGNATURE----- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 virtual primary key, it is slow;
when there is no virtual primary key, it scrolls quickly.
Looking at Profiler, I see that when there is no virtual primary key, Access
sends a simple select command to SQL Server. However, when there is a
virtual...
|
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 and has been in place for several years
without any performance problems.
Recently I added a couple of fields to the output of the view, and it became
very slow when scrolling. When just opened in the database window, the
linked view takes about...
|
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 the appointment
collection to populate the calendar control. The performance getting the
XML data is fine, but loading the data into the collection is slow. My
question/problem is should I be using the collection, a dataset, or
something else to...
|
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 "slow".
I found that the web application will clip along without a problem and then,
for no apparent reason, it will "hang" for 15 to 30 seconds before returning
the page to the client. It appears that the http request is submitted to the...
|
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 first with the areas where the
controls will be displayed cut out and what ever is top most before the form
is shown is momentarity seen through these areas.
This is also noticed if the user changes focus to say internet explorer and
then back to...
| |
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 hang when in the editor? It seams
that every now and then, the IDE seems to stop accepting input (well it is
buffered) as if it is doing some processing. This halt can be anywhere from
half a second to a couple seconds. Very annoying when I am...
|
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 have a reasonably power workstation (3.06Ghz dual Zeon with 4GB RAM and
10K RPM hard drives) -- this problem ONLY occurs immediately after I do a
Clean Solution and then "Build Solution" -- if I just run Build Solutions
(one after another) it only...
|
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
(UAT environment).
Database B is supposed to be faster since it is partitioned.
It has Referential integrity (RI) and indexes defined on it.
Does this have a major impact on INSERTs?
|
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 Domain.
We have both Cold Fusion and ASP.NET apps on this server and the Cold Fusion
apps do not display such slowness on their first start up of the day. Is
there a way to improve the load times of ASP.NET apps?
I'm having to justify to my boss...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |