# Big performance problems of a moderately complex query

**URL:** <https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371>\
**Category:** PostgreSQL General Discussion\
**Created:** [September 30, 2021, 3:16pm UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371 "2021-09-30T15:16:44Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![manstt](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/manstt/32/4615_2.png) [@manstt](https://forums.percona.com/u/manstt)\
**Post date:** [September 30, 2021, 3:16pm UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371/1 "2021-09-30T15:16:45Z")

</div>

Greetings to all,  
I am here to propose a problem that I cannot solve on the postgres database.  
We have an application that originally used the oracle database and we hadn’t noticed any particular speed problems.

We were able to copy the database on the postgres db and everything works without sql errors.

We are noticing some slow query issues in cases like the sql below.

Obviously the fields declared in join and where are indexed as they are declared respectively in the primary key

TABLE1 140.000 rows used space 20M  
TABLE2 184 rows used space 88k

SELECT PRICE1, PRICE2 FROM  
(SELECT FLDTB2\_0070 PRICE1, A.FLDTB1\_3150\_1 PRICE2 FROM TABLE2 A  
JOIN TABLE1 ON FLDTB2\_0010 = FLDTB1\_0010 AND FLDTB2\_0070 = FLDTB1\_0070  
WHERE A.FLDTB1\_0010 = ‘1010’ AND A.FLDTB1\_0293 = (SELECT MAX (B.FLDTB1\_0293)  
FROM TABLE1 B  
WHERE A.FLDTB1\_0010 = B.FLDTB1\_0010 AND A.FLDTB1\_0070 = B.FLDTB1\_0070)) B  
GROUP BY PRICE1, PRICE2;

I tried any parameter both system and postgresql.conf, lots of tools but the time taken is  
2.4 sec while on oracle 34-60 msec

Analyzing the query I see that it wastes a lot of time in the select max

Planning Time: 0.676 ms  
Execution Time: 2.945 ms

This function is requested numerous times so the time multiplies.

Thanks a lot to everyone!  
Recommendations welcome, unfortunately modifying the query would be almost impossible

---

<div class="post-metadata">

**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\
**Post date:** [October 4, 2021, 3:02pm UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371/2 "2021-10-04T15:02:12Z")

</div>

Hi manstt,

Does the oracle and postgresql servers have same CPU , ram and data set size?  
If one of the servers has fewer hardware resources and/or needs to scan a higher amount of rows, execution time will vary.

You mentioned that there were condition uses columns that are indexed, but what about the group by ?  
Can you provide postgresql version used and the schema definition of the tables involved on the query?

Regards

---

<div class="post-metadata">

**Author:** ![manstt](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/manstt/32/4615_2.png) [@manstt](https://forums.percona.com/u/manstt)\
**Post date:** [October 4, 2021, 3:58pm UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371/3 "2021-10-04T15:58:03Z")

</div>

Hello CTutte,

thanks for your concern, yes the servers are identical in ram, cpu and disk speed. Exactly two clones.  
Yes, sure to send the DDL of the table.

I said that I notice a clear difference in the select max part, if I put a fixed value in its place, things change sharply.

CREATE TABLE TABLE1 (  
FLDTB1\_0010 numeric(5) NOT NULL,  
FLDTB1\_0070 varchar(3) NOT NULL,  
FLDTB1\_1051 varchar(8) NULL,  
FLDTB1\_1052 varchar(8) NULL,  
FLDTB1\_4111 timestamp NULL,  
CONSTRAINT TABLE1\_pkey PRIMARY KEY (FLDTB1\_0010, FLDTB1\_0070)  
);

CREATE TABLE TABLE2 (  
FLDTB2\_0010 numeric(5) NOT NULL,  
FLDTB2\_0070 varchar(3) NOT NULL,  
FLDTB2\_0293 timestamp NOT NULL,  
FLDTB2\_3150\_1 numeric(15,8) NULL,  
CONSTRAINT TABLE2\_pkey PRIMARY KEY (FLDTB2\_0010, FLDTB2\_0070, FLDTB2\_0293)  
);

SELECT PREZZO1, PREZZO2 FROM  
( SELECT FLDTB1\_0070 PREZZO1, A.FLDTB2\_3150\_1 PREZZO2 FROM TABLE2 A  
JOIN TABLE1 ON FLDTB1\_0010 = FLDTB2\_0010 AND FLDTB1\_0070 = FLDTB2\_0070  
WHERE A.FLDTB2\_0010 = ‘01010’ AND A.FLDTB2\_0293 = (SELECT MAX(B.FLDTB2\_0293)  
FROM TABLE2 B  
WHERE A.FLDTB2\_0010 = B.FLDTB2\_0010 AND A.FLDTB2\_0070 = B.FLDTB2\_0070 ) ) B  
GROUP BY PREZZO1, PREZZO2;

I also send you the query analysis …

Group (cost=77163.67…77168.92 rows=700 width=11) (actual time=2383.257…2383.274 rows=40 loops=1)  
Group Key: TABLE1.FLDTB1\_0070, a.FLDTB2\_3150\_1  
 → Sort (cost=77163.67…77165.42 rows=700 width=11) (actual time=2383.254…2383.260 rows=40 loops=1)  
Sort Key: TABLE1.FLDTB1\_0070, a.FLDTB2\_3150\_1  
Sort Method: quicksort Memory: 26kB  
 → Merge Join (cost=77090.47…77130.59 rows=700 width=11) (actual time=2383.052…2383.208 rows=40 loops=1)  
Merge Cond: ((TABLE1.FLDTB1\_0070)::text = (a.FLDTB2\_0070)::text)  
 → Index Only Scan using TABLE1\_pkey on TABLE1 (cost=0.14…15.56 rows=184 width=9) (actual time=0.091…0.185 rows=183 loops=1)  
Index Cond: (FLDTB1\_0010 = ‘1010’::numeric)  
Heap Fetches: 183  
 → Sort (cost=77090.24…77091.99 rows=700 width=16) (actual time=2382.947…2382.952 rows=41 loops=1)  
Sort Key: a.FLDTB2\_0070  
Sort Method: quicksort Memory: 27kB  
 → Seq Scan on TABLE2 a (cost=0.00…77057.16 rows=700 width=16) (actual time=10.726…2382.834 rows=41 loops=1)  
Filter: ((FLDTB2\_0010 = ‘1010’::numeric) AND (FLDTB2\_0293 = (SubPlan 2)))  
Rows Removed by Filter: 140038  
SubPlan 2  
 → Result (cost=0.47…0.50 rows=1 width=8) (actual time=0.016…0.016 rows=1 loops=140079)  
InitPlan 1 (returns $2)  
 → Limit (cost=0.42…0.47 rows=1 width=8) (actual time=0.016…0.016 rows=1 loops=140079)  
 → Index Only Scan Backward using TABLE2\_pkey on TABLE2 b (cost=0.42…180.74 rows=3502 width=8) (actual time=0.016…0.016 rows=1 loops=140079)  
Index Cond: ((FLDTB2\_0010 = a.FLDTB2\_0010) AND (FLDTB2\_0070 = (a.FLDTB2\_0070)::text) AND (FLDTB2\_0293 IS NOT NULL))  
Heap Fetches: 140066  
Planning Time: 1.092 ms  
Execution Time: 2383.452 ms

A thousand thanks

---

<div class="post-metadata">

**Author:** ![CTutte](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/ctutte/32/1341_2.png) [@CTutte](https://forums.percona.com/u/CTutte)\
**Post date:** [October 4, 2021, 9:25pm UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371/4 "2021-10-04T21:25:42Z")

</div>

Hi again,

What exact version of postgresql are you using ?  
Can you try running VACUUM FULL ANALYZE and repeat the query to see if there is anything else ongoing ?

The problem looks like the MAX from table2 that is taking the most part of the execution. Can you try adding an index on FLDTB2\_0293 timestamp and re try?

Also, the query is a SELECT (sub query 1 ( sub query 2)) .  
Try running sub query 2 (the max part only), with fixed values and check if execution times makes sense or takes longer than expected.

Regards

---

<div class="post-metadata">

**Author:** ![manstt](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/manstt/32/4615_2.png) [@manstt](https://forums.percona.com/u/manstt)\
**Post date:** [October 5, 2021, 9:24am UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371/5 "2021-10-05T09:24:26Z")

</div>

I am using postgresql 13

I first performed a VACUUM FULL ANALYZE TABLE1 and it took a very long time “duration: 2108217.310 ms”, with  
very few lines changed

while the second VACUUM FULL ANALYZE TABLE2, took a few milliseconds.

Re-launching the vacuum now takes a few milliseconds, I can say that when I launch the query there are no activities in progress, but during the night these tables have large inserts / updates or total reloads.  
So immediately after the vacuum I rerun the query but time  
it always settles on 2.382 seconds.

At this point I have created a new index as you proposed on the TABLE2 field FLDTB2\_0293, but unfortunately things do not  
they change.

I tried running the query only  
SELECT MAX (B.FLDTB2\_0293) FROM TABLE2 B WHERE A.FLDTB2\_0010 = B.FLDTB2\_0010 AND A.FLDTB2\_0070 = B.FLDTB2\_0070

By setting two fixed values ??for A.FLDTB2\_0010 and A.FLDTB2\_0070.  
Record summary TABLE1 184 TABLE2 140.115

Postgres  
The query takes 3-4ms 3ms \* 184 = 0.552 sec

Oracle  
The query takes 2-3ms 2ms \* 184 = 0.368 sec

What then on oracle does not correspond to the total time of the query as it takes from 22 / 32ms, maybe they have query caching system?

Thanks so much for the support.

---

<div class="post-metadata">

**Author:** ![manstt](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/manstt/32/4615_2.png) [@manstt](https://forums.percona.com/u/manstt)\
**Post date:** [October 5, 2021, 1:41pm UTC](https://forums.percona.com/t/big-performance-problems-of-a-moderately-complex-query/12371/6 "2021-10-05T13:41:04Z")

</div>

Hello Carlos,

it seems i found the problem, basically the problem comes from the max subquery.

In the original query and the lack of correct aliases, it seems that in postgres the incorrect declaration of these aliases creates a longer execution time because it crosses the same table several times. I also eliminated the useless first select as the values are conveniently displayed with the right join and max

Obviously this can result as a BUG and therefore I could advance an opening for a ticket for the resolution of the problem.

SELECT FLDTB1\_0070 PREZZO1, A.FLDTB2\_3150\_1 PREZZO2  
FROM TABLE2 A  
JOIN TABLE1 B ON B.FLDTB1\_0010 = A.FLDTB2\_0010 AND B.FLDTB1\_0070 = A.FLDTB2\_0070  
AND A.FLDTB2\_0010 = ‘01010’ AND A.FLDTB2\_0293 = (SELECT MAX(A.FLDTB2\_0293)  
FROM TABLE2 A  
WHERE A.FLDTB2\_0010 = B.FLDTB1\_0010 AND A.FLDTB2\_0070 = B.FLDTB1\_0070 )  
GROUP BY PREZZO1, PREZZO2;