Performance problems with Postgres JDBC 42.4.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




Dear all,

I'm running a query  from Java on a postgres database:

Java version: 17
JDBC version: 42.4.2
Postgres version: 13.1

In parallel I'm testing the same queries from pgAdmin 4 version 6.13

The tables I'm using contains more than 10million rows each and I have two questions here:

1. I need to extract the path of a file without the file itself. For this I use two alternatives as I found that sentence "A" is much faster than the "B" one:

"A" sentence:

SELECT DISTINCT ( LEFT(opf.file_path, length(opf.file_path) - position('/' in reverse(opf.file_path))) ) AS path
                           FROM product AS op JOIN product_file AS opf ON opf.product_id = op.id
                           WHERE op.proprietary_end_date <= CURRENT_DATE AND op.id LIKE 'urn:esa:psa:%'

"B" sentence:

SELECT DISTINCT ( regexp_replace(opf.file_path, '(.*)\/(.*)$', '\1') ) AS path  
                           FROM product AS op JOIN product_file AS opf ON opf.product_id = op.id
                           WHERE op.proprietary_end_date <= CURRENT_DATE AND op.id LIKE 'urn:esa:psa:%'

2. Running sentence "A" on the pgAdmin client takes 4-5 minutes to finish but running it from a Java program it never ends. This is still the case when I limit the output to the first 100 rows so I assume this is not a problem with the amount of data being transferred but the way postgres resolve the query. To make it work in Java I had to define a postgres function that I call from the Java code instead of running the query directly. 

I had a similar problem in the past with a query that performed very poorly from a Java client while it was fine from pgAdmin or a python script. In that case it was a matter of column types not compatible with the JDBC (citext) deriving in an implicit cast that prevented the postgres engine from using a given index or to cast all the values of that column before using it, not sure now. But I don't think this is not the case here.

Could anyone help me again?

Many thanks in advance
Jose



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux