2

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!

EDIT:

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 = '*'
Improve this question
2
  • 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.

Improve this answer
  • The 134 rows were just a simple select like: SELECT * FROM champions – Ferhat Sayan Feb 10 '17 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... – Ferhat Sayan Feb 10 '17 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 – Evan Carroll Feb 10 '17 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? – Ferhat Sayan Feb 10 '17 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? – Evan Carroll Feb 10 '17 at 23:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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