Re: Performance problems with Postgres JDBC 42.4.2

On Mon, 6 Nov 2023 at 09:59, Jose Osinde <jose(dot)osinde(at)gmail(dot)com> wrote:

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

Can you share your java code ?

If you are using a PreparedStatement the driver will use the extended
protocol which may be slower. Statements use SimpleQuery which is faster
and more like pgadmin

Issuing a Query and Processing the Result | pgJDBC (postgresql.org)
<https://jdbc.postgresql.org/documentation/query/#example51processing-a-simple-query-in-jdbc>

<https://jdbc.postgresql.org/documentation/query/#example51processing-a-simple-query-in-jdbc>
Dave

>
>