Big performance problems of a moderately complex query

PostgreSQL PostgreSQL General Discussion
1

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

1 Like
2

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

2 Likes
3

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

4

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

2 Likes
5

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.

1 Like
6

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;

1 Like