I have a Postgres database. When I query the same table with multiple queries asynchronously (concurrently), each query takes longer than if I call the query only once or in a synchronous (sequential) manner. I have tried via a REST API into a EJB server and using psql directly to the database.
For the same query, called 10 times each, this is the result per call. This is the command used: docker exec -i container-name psql -Uusername -d databasename -c "..."
Synchronously (Sequential)
Finished 01 in 0h 0m 5s 134 ms.
Finished 02 in 0h 0m 5s 139 ms.
Finished 03 in 0h 0m 5s 181 ms.
Finished 04 in 0h 0m 5s 270 ms.
Finished 05 in 0h 0m 5s 157 ms.
Finished 06 in 0h 0m 5s 163 ms.
Finished 07 in 0h 0m 5s 178 ms.
Finished 08 in 0h 0m 5s 205 ms.
Finished 09 in 0h 0m 5s 169 ms.
Finished 10 in 0h 0m 5s 155 ms.
Asynchronously (Concurrent)
Finished 03 in 0h 0m 12s 634 ms.
Finished 04 in 0h 0m 18s 87 ms.
Finished 07 in 0h 0m 21s 209 ms.
Finished 01 in 0h 0m 21s 279 ms.
Finished 05 in 0h 0m 21s 302 ms.
Finished 02 in 0h 0m 21s 659 ms.
Finished 09 in 0h 0m 22s 127 ms.
Finished 06 in 0h 0m 22s 394 ms.
Finished 08 in 0h 0m 22s 667 ms.
Finished 10 in 0h 0m 22s 671 ms.
I found this post (Postgres: Concurrent queries in a connection )that mentions concurrent queries but I do not really know how to proceed from here. I have looked at the pg_stat_activity table when querying asynchronously and it has a row per query but I am not sure if this means multiple connections or just multiple queries per connection.
New: This is the script I use to run asynchronously or concurrently. If you remove the '&' then it runs each call to the function sequentially/synchronously.
#!/usr/bin/env bash
set -e
function runScript1() {
_START_T=$(date +%s%N)
docker exec -i container-name psql -Uusername -d databse -c "SELECT ... FROM ..."
_END_T=$(date +%s%N)
_ELAPSED_MS=$(((_END_T - _START_T) / 1000000))
_SECONDS=$(( _ELAPSED_MS / 1000 ))
_MILLISECONDS=$(( _ELAPSED_MS % 1000 ))
_MINUTES=$(( _SECONDS / 60 ))
_HOURS=$(( _MINUTES / 60 ))
# Print the result
echo "Finished $1 in ${_HOURS}h $((_MINUTES % 60))m $((_SECONDS % 60))s $_MILLISECONDS ms."
}
_START_TOTAL=$(date +%s%N)
runScript1 "01" &
runScript1 "02" &
runScript1 "03" &
runScript1 "04" &
runScript1 "05" &
runScript1 "06" &
runScript1 "07" &
runScript1 "08" &
runScript1 "09" &
runScript1 "10" &
_END_TOTAL=$(date +%s%N)
