2

I know that EXPLAIN ANALYZE returns the query time, but it does not appear to do so for my use case. (Unless I am missing something...what could it be...?)

SELECT cars.*, users.*, makers.*, places.*
  FROM cars
  LEFT OUTER JOIN "users" ON "users"."id" = "cars"."user_id" 
  LEFT OUTER JOIN "makers" ON "makers"."id" = "cars"."company_id" 
  LEFT OUTER JOIN "places" ON "places"."id" = "cars"."location_id" 
  WHERE "cars"."deleted_at" IS NULL 
    AND "cars"."draft" = 'f' 
    AND ((cars.reference_number::text ILIKE '%abc123%') 
      OR (makers.name::text ILIKE '%abc123%') 
      OR (places.name::text ILIKE '%abc123%'))  
  ORDER BY cars.id DESC NULLS LAST 
  LIMIT 20 
  OFFSET 0;

EXPLAIN ANALYZE output:

Planning time: 1.300 ms
Execution time: 105.936 ms

SELECT output using \timing:

Time: 2100.704 ms (00:02.101)

It seems that EXPLAIN ANALYZE does not include time to read the data.

How can I get the total time without using SQL (and not PSQL console)?

PostgreSQL 9.6.6

  • 3
    The time reported by explain (analyze) indeed does not include the time it takes to send the data to the SQL client. It is the time the server needed to completely prepare the result so that it could be sent to the driver. As any delay after that is completely up to the combination of the SQL client and the network between client & server it's impossible for explain to show that – a_horse_with_no_name Jul 5 '18 at 18:20
  • So, it takes 100ms to execute the query, and 2,000ms to send the result over the network...that makes more sense. – B Seven Jul 5 '18 at 22:11
0

From a_horse_with_no_name in the comments,

The time reported by explain (analyze) indeed does not include the time it takes to send the data to the SQL client. It is the time the server needed to completely prepare the result so that it could be sent to the driver. As any delay after that is completely up to the combination of the SQL client and the network between client & server it's impossible for explain to show that

That's pretty much all there is too it. One of them is server side, the other network side.

0

If you want to include network and client time, then start and stop your timer in the client; whatever client that might be.

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.