I searched alot for the answer to my question.

I have a table with 134 rows and 4 rows. My EXPLAIN ANALYZE says the execution time was: 0.038ms. But the actual execution time (using DataGrip) was: 123ms

A complex query with multiple joins shows these stats: EXPLAIN ANALYZE execution time: 38.605ms DataGrip execution time: 177ms

My VPS has 2GB ram and 2 vCores.

Is it more likely a problem with my server or my config, or is this behavior normal?

Here is the EXPLAIN ANALAYZE of the simple select:

Seq Scan on champions  (cost=0.00..4.34 rows=134 width=37) (actual time=0.007..0.024 rows=134 loops=1)
Planning time: 0.053 ms
Execution time: 0.044 ms

Here is the log of DataGrip:

[2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

I am really confused!


These are my only changes to my config:

max_connections = 200
shared_buffers = 512MB
effective_cache_size = 1536MB
work_mem = 32MB
maintenance_work_mem = 128MB
min_wal_size = 1GB
max_wal_size = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
listen_addresses = '*'

1 Answer 1

  • EXPLAIN ANALYZE is showing you the planning and execution time.
  • DataGrip is showing you the completion time. This may include,

    1. Network transit
    2. Drawing in the GUI, etc.

Just think, if you have a one-row table with one bytea column that contains 2GB, transferring it across the network or even using TCP/IP will be a substantial transit compared to the planning and execution of the query.

[2017-02-10 21:38:30] 134 rows retrieved starting from 1 in 135ms (execution: 127ms, fetching: 8ms)

That's 134 rows you didn't have to retrieve using EXPLAIN ANALYZE.

  • The 134 rows were just a simple select like: SELECT * FROM champions Feb 10, 2017 at 20:50
  • The most confusing thing is, that the night I wrote the complex query for example it actually ran in 35ms~ now every query needs 120ms~ I don't know what happened. Also if I try to fetch any table in C# or nodejs it also takes 120ms plus... Feb 10, 2017 at 20:51
  • How are you connection pooling? Are you connecting over TCP/IP, or sockets? Does the problem persist to the same degree if you LIMIT 0 Feb 10, 2017 at 20:53
  • Ok so in DataGrip nothing changes with LIMIT 0. The connection pooling is handled by DataGrip or the ADO.NET driver in C# or the nodejs package. However if I run the query on the server in psql it really takes 0.3ms~. The connection is in TCP/IP. So fetching 0 rows and drawing 0 rows can't take 120ms can it? Is it a problem with my server or with my config most likely? Feb 10, 2017 at 21:02
  • Is this being run on the same machine you're running psql on, and you running psql with TCP/IP or Unix sockets? Feb 10, 2017 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.