Amazon Redshift
Database Developer Guide (API Version 2012-12-01)
Did this page help you?  Yes | No |  Tell us about it...
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.

Determining Whether a Query Is Writing to Disk

To determine whether any query steps wrote intermediate results to disk for a particular query, use the following set of system table queries. Use this method for all types of queries that can write to disk, including both SELECT and DELETE statements.

  1. Issue the following query to determine the query ID for the query being investigated:

    select query, elapsed, substring
    from svl_qlog
    order by query
    desc limit 5;

    This query displays the query ID, execution time, and truncated query text for the last five queries to run against the database tables, as shown in the following sample output:

    query| elapsed |                            substring
    1026 | 9574270 | select s.seg, s.maxtime, s.label,
                     s.is_diskbased from query_
    1025 | 18672594| select t1.c1 x1, t2.c2 x2 from tbig t1, tbig
                     t2 where t1.c1
    1024 | 84266   | select count(*) as underrepped from ( select
                     count(*) as a f
    1023 | 83217   | select system_status from stv_gui_status
    1022 | 39236   | select * from stv_sessions
    (5 rows)
  2. Examine the output to determine the query ID that matches the query that you are investigating.

  3. Using the query ID, issue the following query to determine whether any steps for this query wrote to the disk. The following example uses query ID 1025:

    select query, step, rows, workmem, label, is_diskbased
    from svl_query_summary
    where query = 1025 order by workmem desc;

    This query returns the following sample output:

    query| step|  rows  |  workmem  |  label        | is_diskbased
    1025 |  0  |16000000|  43205240 |scan tbl=9     | f
    1025 |  2  |16000000|  43205240 |hash tbl=142   | t
    1025 |  0  |16000000|  55248    |scan tbl=116536| f
    1025 |  2  |16000000|  55248    |dist           | f
    (4 rows)
  4. Look through the output. If IS_DISKBASED is true ("t") for any step, then that step wrote data to disk. In the previous example, the hash step intermediate results were written to disk.

If you find that steps are writing to disk and affecting performance, the easiest solution is to increase the memory available to a query by increasing the slot count for the query. Workload management (WLM) reserves slots in a query queue according to the concurrency level set for the queue (for example, if concurrency level is set to 5, then the query has five slots). WLM allocates the available memory for a query equally to each slot. Slot count is set in the wlm_query_slot_count parameter. Increasing the slot count increases the amount of memory available for the query.