Slow redshift query with low cost and number of rows

Question

I have a Redshift query that results on the following query plan being generated:

XN HashAggregate  (cost=4.00..4.06 rows=1 width=213)
  ->  XN Hash Join DS_DIST_ALL_NONE  (cost=0.02..3.97 rows=1 width=213)
        ->  XN Hash Join DS_DIST_NONE  (cost=0.00..3.93 rows=1 width=213)
        ->  XN Hash  (cost=0.01..0.01 rows=1 width=8)
              ->  XN Seq Scan on response_entities re  (cost=0.00..1.96 rows=157 width=85)
              ->  XN Hash  (cost=0.00..0.00 rows=1 width=208)
                    ->  XN Seq Scan on response_views rv  (cost=0.00..0.00 rows=1 width=208)
              ->  XN Seq Scan on dim_date dd  (cost=0.00..0.01 rows=1 width=8)

The query wouldn't broadcast or redistribute any data, it has a very low cost, and doesn't need to read a large number of rows. It actually doesn't return any rows, and none of its steps are diskbased.

The execution details on the AWS console show this:

I'm not including the query because I'm not looking for a reason why this particular query took 3 seconds to complete. I keep seeing execution timelines similar to this one, I'm trying to understand why even though each step takes just a couple of milliseconds to complete, the query ends up taking much longer. There are no other concurrent queries being executed.

Is all this time spent just on query compilation? Is this expected? Is there something I'm missing?

user1845791 user1845791 · Accepted Answer · 2018-08-27T17:08:30

Query compilation is what to seems the reason for this. This query the slow compiled segment.

select userid, xid,  pid, query, segment, locus,  
datediff(ms, starttime, endtime) as duration, compile 
from svl_compile 
where query = 26540
order by query, segment;

More information on svl_compile can be found here.

And this article explains the same issue and how to reduce number of compilations (or workarounds).

Slow redshift query with low cost and number of rows

1 Answers