Problem

CustomerID is not showing expected data in the user interface or is performing with lower performance than expected. The DBA needs to know what kind of SQL queries are performed during these kinds of operations in order to troubleshoot operations in the RDBMS or determine if the RDBMS engine can be configured for higher performance.

Solution

Application side

Following WildFly configuration will make the SQL queries generated by JPA (Hibernate) visible in the WildFly server.log file.

Example from WildFly standalone.xml file

...
    <profile>
        <subsystem xmlns="urn:jboss:domain:logging:6.0">
...
            <logger category="org.hibernate.SQL">
                <level name="DEBUG"/>
            </logger>
            <logger category="org.hibernate.type">
                <level name="TRACE"/>
            </logger>
...
        </subsystem>
...
    </profile>

The first logger element makes the SQL queries appear in the server.log file (if default configuration is used). However those SQL queries don't have all the dynamic parameters included. The second element will include the parameter values used in the queries as well. So the first one might be enough in some troubleshooting cases and produces less extra log entries.

PostgreSQL side

After you have found the relevant SQL queries performed by the application you can proceed to investigate how PostgreSQL handles them. PostgreSQL provides a way to see the execution plan it creates for a specific query. You can use the EXPLAIN command to examine how the query is run. Note that the query is actually executed against the data so run it inside a transaction that is cancelled using ROLLBACK if you are analyzing queries that modify data unless you want to permanently persist the changes to the database. So use either:

EXPLAIN ANALYZE <your SQL query>;

or

BEGIN;
EXPLAIN ANALYZE <your SQL query>;
ROLLBACK;

You will also see the execution time for the query.

Related articles

Page:

Upgrade and migrate to new version of PostgreSQL
Page:

Use Directory User Mapping with SQL databases
Page:

Configure timeout values for data store connections
Page:

CustomerID SQL query troubleshooting

Browser not supported