Is pgAdmin adding query time overhead?

I just wrote a long question about what I did to optimize a rather simple query that takes much longer than I wish it would. I kept querying with pgAdmin. And then I made the query simpler and simpler until I ended up querying for the primary key of a newly created table with just 1 row in it.

create table perf_test (id bigint primary key);

and then queried:

select
  count(t.id)
from
  perf_test t
where
  t.id = 1
;

and the message output is:

Successfully run. Total query runtime: 66 msec.
1 rows affected.

I need to optimize a query that usually takes around 30-40ms when issued from my application. How can I experiment and measure the performance if the execution time in pgAdmin is already much higher for the simplest of all queries?

Yes, pgAdmin adds a lot of latency overhead. I'm surprised you could even get it as low as 66msec.

The answer is pretty easy, don't use pgAdmin for things like this.

To try to figure out why a query is slow, you should use EXPLAIN (ANALYZE, BUFFERS) on the query. pgAdmin does provide a way to do that, but IME it is strictly worse than just doing it through psql, or by using auto_explain to capture the plan into the log files.

StackExchange Database Administrators Q#316490, answer score: 6