From: Tom Lane <tgl@sss.pgh.pa.us>
To: Satalabaha Postgres <satalabaha.postgres@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Finding query execution time using \timing and EXPLAIN ANALYZE..
Date: Mon, 21 Nov 2022 09:54:01 -0500
Message-ID: <3118192.1669042441@sss.pgh.pa.us> (raw)
In-Reply-To: <CAJ_W8nb1nVNKfo_1p=KQY2U2gUS6E+8+z7Dm=7uHnaR=RFMbYQ@mail.gmail.com>
References: <CAJ_W8nb1nVNKfo_1p=KQY2U2gUS6E+8+z7Dm=7uHnaR=RFMbYQ@mail.gmail.com>
Satalabaha Postgres <satalabaha.postgres@gmail.com> writes:
> I was trying to get elapsed time for the sql using the \timing command in
> psql as well as EXPLAIN ANALYZE.. I see both the commands reporting
> different elapsed time. And this is after repeated execution of the same
> query and not the first execution. In this case, which one would be the
> right execution time for the query.
They're presumably both accurate, but they aren't measuring the same
thing. psql's \timing reports the end-to-end time from the client's
perspective. EXPLAIN ANALYZE reports the time for two components
of the server's processing. So \timing includes all of these:
1. Transmission of the query across the network to the server
2. Parsing and parse analysis of the query
3. Rewriting (a/k/a view expansion) of the query
4. Planning
5. Execution
6. Formatting of the query results as text
7. Transmission of the results across the network to the client.
while EXPLAIN is only telling you about steps 4 and 5.
Having said that, I'm a bit baffled by the size of the difference.
This example doesn't look like any of the other steps should take
very long: it's a short simple query, there doesn't seem to be
any view involved, and the result is only one row. Unless maybe
you're using tin-cans-and-string for the network connection?
There are various effects whereby the first execution of a query
can take longer than repeat executions, but you said you'd
controlled for that.
Also, how stable are these numbers if you repeat them several
times? It might be mostly about noise and/or the effects of
CPU power management. On my Linux box I find that I don't
get very reproducible numbers for short queries unless I've
done "sudo cpupower frequency-set --governor performance".
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, satalabaha.postgres@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: Finding query execution time using \timing and EXPLAIN ANALYZE..
In-Reply-To: <3118192.1669042441@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox