Slow query - PostgreSQL Database

Yonatan Goraly

I have a query that uses the same view 6 times. It seems that the
database engine is calculating the view each time.
The result is very poor performance. The same query takes 2 sec with MS
SQL, and more than 10 sec with PostgreSQL.
Is there a method to improve the performance besides merging the
components of the view into one table?
I believe that caching query results could have resolved this issue

Yonatan Goraly
This is the query:

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_HIERA RCH h
join ALL_FOLDERS_VIE W_NAME f1 on h.FOLDER_ID_1=f 1.ID
join ALL_FOLDERS_VIE W_NAME f2 on h.FOLDER_ID_2=f 2.ID
join ALL_FOLDERS_VIE W_NAME f3 on h.FOLDER_ID_3=f 3.ID
join ALL_FOLDERS_VIE W_NAME f4 on h.FOLDER_ID_4=f 4.ID
join ALL_FOLDERS_VIE W_NAME f5 on h.FOLDER_ID_5=f 5.ID
join ALL_FOLDERS_VIE W_NAME f6 on h.FOLDER_ID_6=f 6.ID;
------------------------------------------------------------------
QUERY PLAN
Nested Loop (cost=421.29..4 26.60 rows=1 width=511)
Join Filter: ("outer".folder _id_6 = "inner".id)
-> Nested Loop (cost=400.22..4 03.10 rows=1 width=447)
Join Filter: ("outer".folder _id_5 = "inner".id)
-> Merge Join (cost=379.16..3 79.35 rows=1 width=383)
Merge Cond: ("outer".folder _id_4 = "inner".id)
-> Sort (cost=355.34..3 55.36 rows=7 width=319)
Sort Key: h.folder_id_4
-> Merge Join (cost=354.74..3 55.23 rows=7 width=319)
Merge Cond: ("outer".folder _id_3 = "inner".id)
-> Sort (cost=330.92..3 31.05 rows=53 width=255)
Sort Key: h.folder_id_3
-> Merge Join (cost=326.71..3 29.41
rows=53 width=255)
Merge Cond: ("outer".folder _id_2 =
"inner".id)
-> Sort (cost=302.89..3 03.84
rows=380 width=191)
Sort Key: h.folder_id_2
-> Merge Join
(cost=268.08..2 86.62 rows=380 width=191)
Merge Cond:
("outer".folder _id_1 = "inner".id)
-> Sort
(cost=244.26..2 51.09 rows=2732 width=127)
Sort Key:
h.folder_id_1
-> Seq Scan on
comn_attr_hiera rch h (cost=0.00..88. 32 rows=2732 width=127)
-> Sort
(cost=23.82..23 .89 rows=28 width=36)
Sort Key: f1.id
-> Subquery
Scan f1 (cost=21.07..23 .15 rows=28 width=36)
->
Unique (cost=21.07..23 .15 rows=28 width=36)
->
Sort (cost=21.07..21 .76 rows=278 width=36)

Sort Key: id name

-> Append (cost=0.00..9.7 8 rows=278 width=36)

-> Subquery Scan "*SELECT* 1" (cost=0.00..3.1 5 rows=115 width=26)

-> Seq Scan on ent_folder (cost=0.00..3.1 5 rows=115 width=26)

-> Subquery Scan "*SELECT* 2" (cost=0.00..1.2 5 rows=25 width=25)

-> Seq Scan on ent_mkt_segment (cost=0.00..1.2 5 rows=25 width=25)

-> Subquery Scan "*SELECT* 3" (cost=0.00..3.2 6 rows=126 width=24)

-> Seq Scan on ent_company (cost=0.00..3.2 6 rows=126 width=24)

-> Subquery Scan "*SELECT* 4" (cost=0.00..1.1 1 rows=11 width=25)

-> Seq Scan on ent_product (cost=0.00..1.1 1 rows=11 width=25)

-> Subquery Scan "*SELECT* 5" (cost=0.00..1.0 1 rows=1 width=36)

-> Seq Scan on ent_environment _type (cost=0.00..1.0 1 rows=1 width=36)
-> Sort (cost=23.82..23 .89
rows=28 width=36)
Sort Key: f2.id
-> Subquery Scan f2
(cost=21.07..23 .15 rows=28 width=36)
-> Unique
(cost=21.07..23 .15 rows=28 width=36)
-> Sort
(cost=21.07..21 .76 rows=278 width=36)
Sort Key:
id name
->
Append (cost=0.00..9.7 8 rows=278 width=36)
->
Subquery Scan "*SELECT* 1" (cost=0.00..3.1 5 rows=115 width=26)

-> Seq Scan on ent_folder (cost=0.00..3.1 5 rows=115 width=26)
->
Subquery Scan "*SELECT* 2" (cost=0.00..1.2 5 rows=25 width=25)

-> Seq Scan on ent_mkt_segment (cost=0.00..1.2 5 rows=25 width=25)
->
Subquery Scan "*SELECT* 3" (cost=0.00..3.2 6 rows=126 width=24)

-> Seq Scan on ent_company (cost=0.00..3.2 6 rows=126 width=24)
->
Subquery Scan "*SELECT* 4" (cost=0.00..1.1 1 rows=11 width=25)

-> Seq Scan on ent_product (cost=0.00..1.1 1 rows=11 width=25)
->
Subquery Scan "*SELECT* 5" (cost=0.00..1.0 1 rows=1 width=36)

-> Seq Scan on ent_environment _type (cost=0.00..1.0 1 rows=1 width=36)
-> Sort (cost=23.82..23 .89 rows=28 width=36)
Sort Key: f3.id
-> Subquery Scan f3 (cost=21.07..23 .15
rows=28 width=36)
-> Unique (cost=21.07..23 .15
rows=28 width=36)
-> Sort (cost=21.07..21 .76
rows=278 width=36)
Sort Key: id name
-> Append
(cost=0.00..9.7 8 rows=278 width=36)
-> Subquery
Scan "*SELECT* 1" (cost=0.00..3.1 5 rows=115 width=26)
-> Seq
Scan on ent_folder (cost=0.00..3.1 5 rows=115 width=26)
-> Subquery
Scan "*SELECT* 2" (cost=0.00..1.2 5 rows=25 width=25)
-> Seq
Scan on ent_mkt_segment (cost=0.00..1.2 5 rows=25 width=25)
-> Subquery
Scan "*SELECT* 3" (cost=0.00..3.2 6 rows=126 width=24)
-> Seq
Scan on ent_company (cost=0.00..3.2 6 rows=126 width=24)
-> Subquery
Scan "*SELECT* 4" (cost=0.00..1.1 1 rows=11 width=25)
-> Seq
Scan on ent_product (cost=0.00..1.1 1 rows=11 width=25)
-> Subquery
Scan "*SELECT* 5" (cost=0.00..1.0 1 rows=1 width=36)
-> Seq
Scan on ent_environment _type (cost=0.00..1.0 1 rows=1 width=36)
-> Sort (cost=23.82..23 .89 rows=28 width=36)
Sort Key: f4.id
-> Subquery Scan f4 (cost=21.07..23 .15 rows=28
width=36)
-> Unique (cost=21.07..23 .15 rows=28 width=36)
-> Sort (cost=21.07..21 .76 rows=278
width=36)
Sort Key: id name
-> Append (cost=0.00..9.7 8
rows=278 width=36)
-> Subquery Scan "*SELECT*
1" (cost=0.00..3.1 5 rows=115 width=26)
-> Seq Scan on
ent_folder (cost=0.00..3.1 5 rows=115 width=26)
-> Subquery Scan "*SELECT*
2" (cost=0.00..1.2 5 rows=25 width=25)
-> Seq Scan on
ent_mkt_segment (cost=0.00..1.2 5 rows=25 width=25)
-> Subquery Scan "*SELECT*
3" (cost=0.00..3.2 6 rows=126 width=24)
-> Seq Scan on
ent_company (cost=0.00..3.2 6 rows=126 width=24)
-> Subquery Scan "*SELECT*
4" (cost=0.00..1.1 1 rows=11 width=25)
-> Seq Scan on
ent_product (cost=0.00..1.1 1 rows=11 width=25)
-> Subquery Scan "*SELECT*
5" (cost=0.00..1.0 1 rows=1 width=36)
-> Seq Scan on
ent_environment _type (cost=0.00..1.0 1 rows=1 width=36)
-> Subquery Scan f5 (cost=21.07..23 .15 rows=28 width=36)
-> Unique (cost=21.07..23 .15 rows=28 width=36)
-> Sort (cost=21.07..21 .76 rows=278 width=36)
Sort Key: id name
-> Append (cost=0.00..9.7 8 rows=278 width=36)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..3.1 5 rows=115 width=26)
-> Seq Scan on ent_folder
(cost=0.00..3.1 5 rows=115 width=26)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..1.2 5 rows=25 width=25)
-> Seq Scan on ent_mkt_segment
(cost=0.00..1.2 5 rows=25 width=25)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..3.2 6 rows=126 width=24)
-> Seq Scan on ent_company
(cost=0.00..3.2 6 rows=126 width=24)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..1.1 1 rows=11 width=25)
-> Seq Scan on ent_product
(cost=0.00..1.1 1 rows=11 width=25)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..1.0 1 rows=1 width=36)
-> Seq Scan on
ent_environment _type (cost=0.00..1.0 1 rows=1 width=36)
-> Subquery Scan f6 (cost=21.07..23 .15 rows=28 width=36)
-> Unique (cost=21.07..23 .15 rows=28 width=36)
-> Sort (cost=21.07..21 .76 rows=278 width=36)
Sort Key: id name
-> Append (cost=0.00..9.7 8 rows=278 width=36)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..3.1 5 rows=115 width=26)
-> Seq Scan on ent_folder
(cost=0.00..3.1 5 rows=115 width=26)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..1.2 5 rows=25 width=25)
-> Seq Scan on ent_mkt_segment
(cost=0.00..1.2 5 rows=25 width=25)
-> Subquery Scan "*SELECT* 3"
(cost=0.00..3.2 6 rows=126 width=24)
-> Seq Scan on ent_company
(cost=0.00..3.2 6 rows=126 width=24)
-> Subquery Scan "*SELECT* 4"
(cost=0.00..1.1 1 rows=11 width=25)
-> Seq Scan on ent_product
(cost=0.00..1.1 1 rows=11 width=25)
-> Subquery Scan "*SELECT* 5"
(cost=0.00..1.0 1 rows=1 width=36)
-> Seq Scan on ent_environment _type
(cost=0.00..1.0 1 rows=1 width=36)
------------------------------------------------------------------
This is the view:

create view ALL_FOLDERS_VIE W_NAME as
select ID, NAME
from ENT_FOLDER
union
select ID, NAME
from ENT_MKT_SEGMENT
union
select ID, NAME
from ENT_COMPANY
union
select ID, NAME
from ENT_PRODUCT
union
select ID, NAME
from ENT_ENVIRONMENT _TYPE;

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #1

Subscribe Reply

1946

Alvaro Herrera

On Mon, Oct 27, 2003 at 12:08:31AM +0200, Yonatan Goraly wrote:

I have a query that uses the same view 6 times. It seems that the
database engine is calculating the view each time.
The result is very poor performance. The same query takes 2 sec with MS
SQL, and more than 10 sec with PostgreSQL.
Is there a method to improve the performance besides merging the
components of the view into one table?

Say, have you ANALYZEd your tables ?

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Coge la flor que hoy nace alegre, ufana. ¿Quién sabe si nacera otra mañana?"

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05 #2

Yonatan Goraly

yes

Alvaro Herrera wrote:

On Mon, Oct 27, 2003 at 12:08:31AM +0200, Yonatan Goraly wrote:

I have a query that uses the same view 6 times. It seems that the
database engine is calculating the view each time.
The result is very poor performance. The same query takes 2 sec with MS
SQL, and more than 10 sec with PostgreSQL.
Is there a method to improve the performance besides merging the
components of the view into one table?

Say, have you ANALYZEd your tables ?

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

Nov 12 '05 #3

Similar topics

3146

why does this comparison script run so slow??

by: Shay | last post by:

essentially I am trying to do some counts based on some assumptions in the recordset. So I get the RS back, put the values into a variable, move to the next record in the RS and compare what is in the variable to the value in the next record in the recordset and do a count. Then overwrite the value in the variables and do the same for the next record and so. But this runs extremly slow. 5000 records takes about 10 minutes in IE6 and I...

ASP / Active Server Pages

5352

Query optimisation question - two queries, one slow, one fast

by: Tim Fountain | last post by:

We've recently enabled slow query logging on a server and it's proving interesting seeing which queries are bogging things down. This one is puzzling me a little: SELECT articleid, type, authorid, authorname, text, posted FROM comments WHERE status = 'normal' ORDER BY posted DESC LIMIT 5; The purpose of this query is to list the five most recent (non-deleted) comments. Here is the table structure:

MySQL Database

1653

Yet another slow-query question.

by: Thomas Bartkus | last post by:

Is it possible that the elapsed time being measured includes waiting for the client to acknowledge that it has received all the data? In *addition* to the server execution time? Documentation seems to *imply* that the slow query log only looks at server execution time. But, it doesn't acknowledge this directly and there seems to be a distinct connection between slow network pipes and slow queries. For example - even the simplest...

MySQL Database

6238

A query runs fast in Query analuser but slow in APplication

by: AG | last post by:

I am able to run a query which runs FAst in QA but slow in the application.It takes about 16 m in QA but 1000 ms on the Application.What I wanted to know is why would the query take a long time in the application when it runs fast on SQL server? How should we try debugging it? Ajay

Microsoft SQL Server

2021

Slow performance with no apparent reason

by: Yonatan Goraly | last post by:

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

PostgreSQL Database

4806

Query very slow because of called function

by: Janross | last post by:

I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. Obviously not workable! I know where the problem is, I just don't know how to fix it. The query calls a function, and I assume it gets slow because the function runs on every record. So--is there a way to rewrite the function so it's quicker?...

Microsoft Access / VBA

2891

datagrid too slow

by: Jennyfer J Barco | last post by:

In my application I have a datagrid. The code calls a Stored procedure and brings like 200 records. I created a dataset and then a dataview to bind the results of the query to my grid using MyGrid.DataBind() Once the records are loaded, to handle the next, previous button is too slow. I have in the same screen OptionsBox and everytime I click in any option I show some text fields in the screen. Anything the user does is very slow. When...

ASP.NET

2676

Some slow-query-log entries don't make sense

by: Dave Hammond | last post by:

Hi All, I'm trying to use the slow-query-log (with --log-queries-not-using-indexes enabled) to determine if any queries need optimization, and have a few questions about some entries I'm seeing. First question: given a table defined as: CREATE TABLE `oa_location` (

MySQL Database

19161

Why is a simple WHERE clause so slow?

by: Emin | last post by:

Dear Experts, I have a fairly simple query in which adding a where clause slows things down by at least a factor of 100. The following is the slow version of the query ------------------------- SELECT * FROM ( Select x.event_date From x FULL OUTER JOIN y ON x.event_date = y.event_date

Microsoft SQL Server

9846

Query Design View is Slow to Open

by: existential.philosophy | last post by:

This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view and datasheet view. From these facts, I conclude that Access is evaluating the query when I go to...

Microsoft Access / VBA

9546

Changing the language in Windows 10

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

Windows Server

10491

Problem With Comparison Operator <=> in G++

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

C / C++

10268

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

10247

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7571

Access Europe - Using VBA to create a class based on a table - Wed 1 May

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

Microsoft Access / VBA

5593

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

4146

transfer the data from one system to another through ip address

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

C# / C Sharp

3762

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2941

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General