Postgresql – How to accurately measure query time in PostgreSQL (because EXPLAIN ANALYZE does not seem to do it)

explainoptimizationperformancepostgresqlquery-performance

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

Best Answer

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.

Related Solutions

Postgresql – Optimize and understand this Query Explain – POSTGRESQL

Work from inside out on the query plan. Anything you see a seq scan on, fix. Look at adding indexes
Use count(). If you're sum(CASE cond THEN 1)ing, then just count(cond)
If you're reducing the count to a bool case, then use a boolean aggregate. rather than count(cond) > 0, just bool_or(cond)

Think about some of your query. What does this even do? I mean on this one you had me thinking he has to be joking... It's not that's it's invalid, it's just crazy complex.

group by c_invoice_id having SUM(CASE WHEN qtyinvoiced > 0 THEN 1 ELSE 0 END) > 0 and SUM(CASE WHEN qtyinvoiced < 0 THEN 1 ELSE 0 END) > 0) as a

--can be rewitten
group by c_invoice_id having count(qtyinvoiced > 0) > 0 and count(qtyinvoiced < 0) > 0) as a

--can be 
group by c_invoice_id having bool_or(qtyinvoiced > 0) and bool_or(qtyinvoiced < 0) as a

Also consider using USING (a), rather than ON (l.a = r.a)

Mysql – Why are these two queries having such different executions

LEFT is killing performance; remove it unless you have a good reason for keeping it. To elaborate...

((but first... The purpose for the LEFT was missing from the original query; this answer assumes the LEFT was not necessary.))

LEFT JOIN says that you want data from the right table, whether or not there was a match with the left table.

JOIN says to display only the rows that match (via ON) both tables.

(OUTER is optional and adds no semantics.)

If you are running a version before 5.6, the derived table (subquery in LEFT JOIN) will have no index, so it must be scanned repeatedly. This is a big reason to get rid of LEFT.

Without the LEFT, the Optimizer is likely to evaluate the subquery once, then efficiently JOIN to reporter to finish the query.

For JOIN, the inner query needs this composite (and covering) index INDEX(messagetypeenum, createdOn, reporterid).

There is another technique (I think)... Get rid of the inner SELECT, simply JOIN (or LEFT JOIN) to the table:

select  r.*
    from  reporter r
    left join  nubamessage m  ON m.reporterid = r.id
      AND  m.messagetypeenum = 7
      and  m.createdOn >= '2016-06-18 00:00:00'
      and  m.createdOn <= '2016-06-18 09:30:00'
    WHERE  r.enabled = 1
      AND  m.reporterid is null;

In this case, it may needs INDEX(reporterid, messagetypeenum, createdOn).

Yet another variant would use EXISTS and, I think, provides the equivalent of the LEFT

select  r.*
    from  reporter r
    WHERE  r.enabled=1
      AND NOT EXISTS 
    (
        SELECT  *
            from  nubamessage m
            where  m.messagetypeenum =7
              and  m.createdOn>='2016-06-18 00:00:00'
              and  m.createdOn<='2016-06-18 09:30:00'
              AND  m.reporterid=r.id 
    )

I can't predict which variant will be fastest. It partially depends on how "many" in the many:1 mapping of m.reporterid : r.id.

Best Answer

Related Solutions

Postgresql – Optimize and understand this Query Explain – POSTGRESQL

Mysql – Why are these two queries having such different executions

Related Question