Skip to content

Instantly share code, notes, and snippets.

@dsaiztc

dsaiztc/queries.sql

Forked from iconara/queries.sql
Last active July 2, 2019 15:51
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dsaiztc/7a10912c4959c8782c1c94a3de5aac74 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dsaiztc/7a10912c4959c8782c1c94a3de5aac74 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @iconara iconara revised this gist Apr 13, 2018. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -203,4 +203,10 @@ SELECT
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'delete') AS "delete",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'references') AS "references"
    FROM users, objects
    ORDER BY full_object_name;
    ORDER BY full_object_name;

    -- Find tables that need vacuum or analyze
    SELECT "database", "schema", "table", unsorted, stats_off
    FROM svv_table_info
    WHERE unsorted > 20
    OR stats_off > 20
  2. @iconara iconara revised this gist Apr 13, 2018. 1 changed file with 32 additions and 33 deletions.
    65 changes: 32 additions & 33 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -171,37 +171,36 @@ WHERE TRIM(status) = 'Running'
    AND event = 'initiating session';

    -- Show permissions
    SELECT *
    FROM (
    SELECT
    schema_name,
    object_name,
    object_type,
    user_name,
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'select') AS "select",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'insert') AS "insert",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'update') AS "update",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'delete') AS "delete",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'references') AS "references"
    FROM
    (SELECT usename AS user_name FROM pg_user) AS users,
    (
    SELECT
    schemaname AS schema_name,
    'table' AS object_type,
    tablename AS object_name,
    schemaname + '.' + tablename AS full_object_name
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT
    schemaname AS schema_name,
    'view' AS object_type,
    viewname AS object_name,
    schemaname + '.' + viewname AS full_object_name
    FROM pg_views
    WHERE schemaname NOT IN ('pg_internal')
    ) AS objects
    ORDER BY full_object_name
    WITH
    users AS (
    SELECT usename AS user_name FROM pg_user
    ),
    objects AS (
    SELECT
    schemaname AS schema_name,
    'table' AS object_type,
    tablename AS object_name,
    schemaname + '.' + tablename AS full_object_name
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT
    schemaname AS schema_name,
    'view' AS object_type,
    viewname AS object_name,
    schemaname + '.' + viewname AS full_object_name
    FROM pg_views
    WHERE schemaname NOT IN ('pg_internal')
    )
    WHERE ("select" OR "insert" OR "update" OR "delete" OR "references");
    SELECT
    schema_name,
    object_name,
    object_type,
    user_name,
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'select') AS "select",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'insert') AS "insert",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'update') AS "update",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'delete') AS "delete",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'references') AS "references"
    FROM users, objects
    ORDER BY full_object_name;
  3. @iconara iconara revised this gist Apr 11, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -188,15 +188,15 @@ FROM (
    (
    SELECT
    schemaname AS schema_name,
    't' AS object_type,
    'table' AS object_type,
    tablename AS object_name,
    schemaname + '.' + tablename AS full_object_name
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT
    schemaname AS schema_name,
    'v' AS object_type,
    'view' AS object_type,
    viewname AS object_name,
    schemaname + '.' + viewname AS full_object_name
    FROM pg_views
  4. @iconara iconara revised this gist Apr 11, 2018. 1 changed file with 20 additions and 19 deletions.
    39 changes: 20 additions & 19 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -174,33 +174,34 @@ AND event = 'initiating session';
    SELECT *
    FROM (
    SELECT
    schemaname AS "schema",
    objectname AS "object",
    usename AS "user",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'select') AS "select",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'insert') AS "insert",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'update') AS "update",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'delete') AS "delete",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'references') AS "references"
    schema_name,
    object_name,
    object_type,
    user_name,
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'select') AS "select",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'insert') AS "insert",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'update') AS "update",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'delete') AS "delete",
    HAS_TABLE_PRIVILEGE(users.user_name, full_object_name, 'references') AS "references"
    FROM
    (SELECT * FROM pg_user) AS users,
    (SELECT usename AS user_name FROM pg_user) AS users,
    (
    SELECT
    schemaname,
    't' AS obj_type,
    tablename AS objectname,
    schemaname + '.' + tablename AS fullobj
    schemaname AS schema_name,
    't' AS object_type,
    tablename AS object_name,
    schemaname + '.' + tablename AS full_object_name
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT
    schemaname,
    'v' AS obj_type,
    viewname AS objectname,
    schemaname + '.' + viewname AS fullobj
    schemaname AS schema_name,
    'v' AS object_type,
    viewname AS object_name,
    schemaname + '.' + viewname AS full_object_name
    FROM pg_views
    WHERE schemaname NOT IN ('pg_internal')
    ) AS objects
    ORDER BY fullobj
    )
    ORDER BY full_object_name
    )
    WHERE ("select" OR "insert" OR "update" OR "delete" OR "references");
  5. @iconara iconara revised this gist Apr 11, 2018. 1 changed file with 37 additions and 2 deletions.
    39 changes: 37 additions & 2 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -153,7 +153,7 @@ SELECT
    r.query AS sql
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;

    -- See the remote host and port of running queries
    -- Show the remote host and port of running queries
    SELECT
    recents.pid,
    TRIM(db_name) AS db,
    @@ -168,4 +168,39 @@ FROM stv_recents recents
    LEFT JOIN stl_connection_log connections ON (recents.pid = connections.pid)
    LEFT JOIN stv_inflight inflight ON recents.pid = inflight.pid
    WHERE TRIM(status) = 'Running'
    AND event = 'initiating session';
    AND event = 'initiating session';

    -- Show permissions
    SELECT *
    FROM (
    SELECT
    schemaname AS "schema",
    objectname AS "object",
    usename AS "user",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'select') AS "select",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'insert') AS "insert",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'update') AS "update",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'delete') AS "delete",
    HAS_TABLE_PRIVILEGE(users.usename, fullobj, 'references') AS "references"
    FROM
    (SELECT * FROM pg_user) AS users,
    (
    SELECT
    schemaname,
    't' AS obj_type,
    tablename AS objectname,
    schemaname + '.' + tablename AS fullobj
    FROM pg_tables
    WHERE schemaname NOT IN ('pg_internal')
    UNION
    SELECT
    schemaname,
    'v' AS obj_type,
    viewname AS objectname,
    schemaname + '.' + viewname AS fullobj
    FROM pg_views
    WHERE schemaname NOT IN ('pg_internal')
    ) AS objects
    ORDER BY fullobj
    )
    WHERE ("select" OR "insert" OR "update" OR "delete" OR "references");
  6. @iconara iconara revised this gist Apr 24, 2016. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -143,14 +143,14 @@ ORDER BY 1, 2, 3, 4;

    -- Currently executing and recently executed queries with status, duration, database, etc.
    SELECT
    recent.pid,
    r.pid,
    TRIM(status) AS status,
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    TRIM(label) AS query_group,
    starttime AS start_time,
    recents.duration,
    query AS sql
    r.starttime AS start_time,
    r.duration,
    r.query AS sql
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;

    -- See the remote host and port of running queries
  7. @iconara iconara revised this gist Feb 19, 2016. 1 changed file with 7 additions and 5 deletions.
    12 changes: 7 additions & 5 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -159,11 +159,13 @@ SELECT
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    TRIM(label) AS query_group,
    starttime AS start_time,
    recents.starttime AS start_time,
    recents.duration,
    query AS sql,
    remotehost AS remote_host,
    remoteport AS remote_port
    FROM stv_recents recents LEFT JOIN stl_connection_log connections ON (recents.pid = connections.pid)
    recents.query AS sql,
    TRIM(remotehost) AS remote_host,
    TRIM(remoteport) AS remote_port
    FROM stv_recents recents
    LEFT JOIN stl_connection_log connections ON (recents.pid = connections.pid)
    LEFT JOIN stv_inflight inflight ON recents.pid = inflight.pid
    WHERE TRIM(status) = 'Running'
    AND event = 'initiating session';
  8. @iconara iconara revised this gist Feb 19, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -155,7 +155,7 @@ FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;

    -- See the remote host and port of running queries
    SELECT
    recent.pid,
    recents.pid,
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    TRIM(label) AS query_group,
  9. @iconara iconara revised this gist Feb 19, 2016. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -143,6 +143,7 @@ ORDER BY 1, 2, 3, 4;

    -- Currently executing and recently executed queries with status, duration, database, etc.
    SELECT
    recent.pid,
    TRIM(status) AS status,
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    @@ -154,6 +155,7 @@ FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;

    -- See the remote host and port of running queries
    SELECT
    recent.pid,
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    TRIM(label) AS query_group,
  10. @iconara iconara revised this gist Feb 19, 2016. 1 changed file with 7 additions and 7 deletions.
    14 changes: 7 additions & 7 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -144,13 +144,13 @@ ORDER BY 1, 2, 3, 4;
    -- Currently executing and recently executed queries with status, duration, database, etc.
    SELECT
    TRIM(status) AS status,
    r.starttime AS start_time,
    duration,
    db_name AS db,
    user_name AS "user"
    r.query AS sql,
    TRIM(label) AS query_group
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    TRIM(label) AS query_group,
    starttime AS start_time,
    recents.duration,
    query AS sql
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;

    -- See the remote host and port of running queries
    SELECT
  11. @iconara iconara revised this gist Feb 19, 2016. 1 changed file with 5 additions and 3 deletions.
    8 changes: 5 additions & 3 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -144,9 +144,10 @@ ORDER BY 1, 2, 3, 4;
    -- Currently executing and recently executed queries with status, duration, database, etc.
    SELECT
    TRIM(status) AS status,
    r.starttime,
    r.starttime AS start_time,
    duration,
    db_name AS "database",
    db_name AS db,
    user_name AS "user"
    r.query AS sql,
    TRIM(label) AS query_group
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;
    @@ -155,9 +156,10 @@ SELECT
    SELECT
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    TRIM(label) AS query_group,
    starttime AS start_time,
    recents.duration,
    query,
    query AS sql,
    remotehost AS remote_host,
    remoteport AS remote_port
    FROM stv_recents recents LEFT JOIN stl_connection_log connections ON (recents.pid = connections.pid)
  12. @iconara iconara revised this gist Feb 19, 2016. 1 changed file with 14 additions and 1 deletion.
    15 changes: 14 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -149,4 +149,17 @@ SELECT
    db_name AS "database",
    r.query AS sql,
    TRIM(label) AS query_group
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;

    -- See the remote host and port of running queries
    SELECT
    TRIM(db_name) AS db,
    TRIM(user_name) AS "user",
    starttime AS start_time,
    recents.duration,
    query,
    remotehost AS remote_host,
    remoteport AS remote_port
    FROM stv_recents recents LEFT JOIN stl_connection_log connections ON (recents.pid = connections.pid)
    WHERE TRIM(status) = 'Running'
    AND event = 'initiating session';
  13. @iconara iconara revised this gist Feb 9, 2016. 1 changed file with 14 additions and 13 deletions.
    27 changes: 14 additions & 13 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -92,16 +92,17 @@ GROUP BY 1, 2
    ORDER BY 1 DESC;

    -- Query duration stats per database, user and query group; including the max, median, 99 percentile, etc.
    -- Change which duration to use (queue, exec or total) by commenting out the right lines below
    WITH
    durations1 AS (
    SELECT
    TRIM("database") AS db,
    TRIM(u.usename) AS "user",
    TRIM(label) AS query_group,
    DATE_TRUNC('day', starttime) AS day,
    total_queue_time/1000000.0 AS queue_duration,
    total_exec_time/1000000.0 AS exec_duration,
    (total_queue_time + total_exec_time)/1000000.0 AS total_duration
    -- total_queue_time/1000000.0 AS duration,
    -- total_exec_time/1000000.0 AS duration,
    (total_queue_time + total_exec_time)/1000000.0 AS duration
    FROM stl_query q, stl_wlm_query w, pg_user u
    WHERE q.query = w.query
    AND q.userid = u.usesysid
    @@ -113,29 +114,29 @@ durations2 AS (
    "user",
    query_group,
    day,
    total_duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p99,
    PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p999
    duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY db, "user", query_group, day) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY db, "user", query_group, day) AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY db, "user", query_group, day) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY db, "user", query_group, day) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY db, "user", query_group, day) AS p99,
    PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY duration) OVER (PARTITION BY db, "user", query_group, day) AS p999
    FROM durations1
    )
    SELECT
    db,
    "user",
    query_group,
    day,
    MIN(total_duration) AS min,
    AVG(total_duration) AS avg,
    MIN(duration) AS min,
    AVG(duration) AS avg,
    MAX(median) AS median,
    MAX(p75) AS p75,
    MAX(p90) AS p90,
    MAX(p95) AS p95,
    MAX(p99) AS p99,
    MAX(p999) AS p999,
    MAX(total_duration) AS max
    MAX(duration) AS max
    FROM durations2
    GROUP BY 1, 2, 3, 4
    ORDER BY 1, 2, 3, 4;
  14. @iconara iconara revised this gist Feb 9, 2016. 1 changed file with 18 additions and 14 deletions.
    32 changes: 18 additions & 14 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -99,39 +99,43 @@ durations1 AS (
    TRIM(u.usename) AS "user",
    TRIM(label) AS query_group,
    DATE_TRUNC('day', starttime) AS day,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration
    FROM stl_query q, pg_user u
    WHERE u.usesysid = q.userid AND querytxt LIKE 'SELECT%' AND "database" != 'dev'
    total_queue_time/1000000.0 AS queue_duration,
    total_exec_time/1000000.0 AS exec_duration,
    (total_queue_time + total_exec_time)/1000000.0 AS total_duration
    FROM stl_query q, stl_wlm_query w, pg_user u
    WHERE q.query = w.query
    AND q.userid = u.usesysid
    AND aborted = 0
    ),
    durations2 AS (
    SELECT
    db,
    "user",
    query_group,
    day,
    duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p99,
    PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p999
    total_duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p99,
    PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY total_duration) OVER (PARTITION BY db, "user", query_group, day) AS p999
    FROM durations1
    )
    )
    SELECT
    db,
    "user",
    query_group,
    day,
    MIN(duration) AS min,
    AVG(duration) AS avg,
    MIN(total_duration) AS min,
    AVG(total_duration) AS avg,
    MAX(median) AS median,
    MAX(p75) AS p75,
    MAX(p90) AS p90,
    MAX(p95) AS p95,
    MAX(p99) AS p99,
    MAX(p999) AS p999,
    MAX(duration) AS max
    MAX(total_duration) AS max
    FROM durations2
    GROUP BY 1, 2, 3, 4
    ORDER BY 1, 2, 3, 4;
  15. @iconara iconara revised this gist Feb 9, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -101,7 +101,7 @@ durations1 AS (
    DATE_TRUNC('day', starttime) AS day,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration
    FROM stl_query q, pg_user u
    WHERE u.usesysid = q.userid and label = 'default' AND querytxt LIKE 'SELECT%' AND "database" != 'dev'
    WHERE u.usesysid = q.userid AND querytxt LIKE 'SELECT%' AND "database" != 'dev'
    ),
    durations2 AS (
    SELECT
  16. @iconara iconara revised this gist Feb 9, 2016. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -96,7 +96,7 @@ WITH
    durations1 AS (
    SELECT
    TRIM("database") AS db,
    TRIM(u.usename) AS user,
    TRIM(u.usename) AS "user",
    TRIM(label) AS query_group,
    DATE_TRUNC('day', starttime) AS day,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration
    @@ -106,7 +106,7 @@ durations1 AS (
    durations2 AS (
    SELECT
    db,
    user,
    "user",
    query_group,
    day,
    duration,
    @@ -120,7 +120,7 @@ durations2 AS (
    )
    SELECT
    db,
    user,
    "user",
    query_group,
    day,
    MIN(duration) AS min,
  17. @iconara iconara revised this gist Feb 9, 2016. 1 changed file with 7 additions and 7 deletions.
    14 changes: 7 additions & 7 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -91,13 +91,13 @@ FROM stl_ddltext
    GROUP BY 1, 2
    ORDER BY 1 DESC;

    -- Query duration stats per database, user and WLM queue; including the max, median, 99 percentile, etc.
    -- Query duration stats per database, user and query group; including the max, median, 99 percentile, etc.
    WITH
    durations1 AS (
    SELECT
    TRIM("database") AS db,
    TRIM(u.usename) AS "user",
    TRIM(label) AS label,
    TRIM(u.usename) AS user,
    TRIM(label) AS query_group,
    DATE_TRUNC('day', starttime) AS day,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration
    FROM stl_query q, pg_user u
    @@ -106,8 +106,8 @@ durations1 AS (
    durations2 AS (
    SELECT
    db,
    "user",
    label,
    user,
    query_group,
    day,
    duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS median,
    @@ -120,8 +120,8 @@ durations2 AS (
    )
    SELECT
    db,
    "user",
    label,
    user,
    query_group,
    day,
    MIN(duration) AS min,
    AVG(duration) AS avg,
  18. @iconara iconara revised this gist Feb 9, 2016. 1 changed file with 11 additions and 5 deletions.
    16 changes: 11 additions & 5 deletions queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -91,19 +91,23 @@ FROM stl_ddltext
    GROUP BY 1, 2
    ORDER BY 1 DESC;

    -- Query duration stats including the max, median, 99 percentile, etc.
    -- Query duration stats per database, user and WLM queue; including the max, median, 99 percentile, etc.
    WITH
    durations1 AS (
    SELECT
    TRIM("database") AS db,
    TRIM(u.usename) AS "user",
    TRIM(label) AS label,
    DATE_TRUNC('day', starttime) AS day,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration
    FROM stl_query
    WHERE label = 'default' AND querytxt LIKE 'SELECT%' AND "database" != 'dev'
    FROM stl_query q, pg_user u
    WHERE u.usesysid = q.userid and label = 'default' AND querytxt LIKE 'SELECT%' AND "database" != 'dev'
    ),
    durations2 AS (
    SELECT
    db,
    "user",
    label,
    day,
    duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS median,
    @@ -116,6 +120,8 @@ durations2 AS (
    )
    SELECT
    db,
    "user",
    label,
    day,
    MIN(duration) AS min,
    AVG(duration) AS avg,
    @@ -127,8 +133,8 @@ SELECT
    MAX(p999) AS p999,
    MAX(duration) AS max
    FROM durations2
    GROUP BY 1, 2
    ORDER BY 1, 2
    GROUP BY 1, 2, 3, 4
    ORDER BY 1, 2, 3, 4;

    -- Currently executing and recently executed queries with status, duration, database, etc.
    SELECT
  19. @iconara iconara revised this gist Jan 14, 2016. 1 changed file with 11 additions and 1 deletion.
    12 changes: 11 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -128,4 +128,14 @@ SELECT
    MAX(duration) AS max
    FROM durations2
    GROUP BY 1, 2
    ORDER BY 1, 2
    ORDER BY 1, 2

    -- Currently executing and recently executed queries with status, duration, database, etc.
    SELECT
    TRIM(status) AS status,
    r.starttime,
    duration,
    db_name AS "database",
    r.query AS sql,
    TRIM(label) AS query_group
    FROM stv_recents r LEFT JOIN stv_inflight i ON r.pid = i.pid;
  20. @iconara iconara revised this gist Dec 21, 2015. 1 changed file with 40 additions and 1 deletion.
    41 changes: 40 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -89,4 +89,43 @@ SELECT
    LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql
    FROM stl_ddltext
    GROUP BY 1, 2
    ORDER BY 1 DESC;
    ORDER BY 1 DESC;

    -- Query duration stats including the max, median, 99 percentile, etc.
    WITH
    durations1 AS (
    SELECT
    TRIM("database") AS db,
    DATE_TRUNC('day', starttime) AS day,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration
    FROM stl_query
    WHERE label = 'default' AND querytxt LIKE 'SELECT%' AND "database" != 'dev'
    ),
    durations2 AS (
    SELECT
    db,
    day,
    duration,
    PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p75,
    PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p90,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p99,
    PERCENTILE_CONT(0.999) WITHIN GROUP (ORDER BY DURATION) OVER (PARTITION BY db, day) AS p999
    FROM durations1
    )
    SELECT
    db,
    day,
    MIN(duration) AS min,
    AVG(duration) AS avg,
    MAX(median) AS median,
    MAX(p75) AS p75,
    MAX(p90) AS p90,
    MAX(p95) AS p95,
    MAX(p99) AS p99,
    MAX(p999) AS p999,
    MAX(duration) AS max
    FROM durations2
    GROUP BY 1, 2
    ORDER BY 1, 2
  21. @iconara iconara revised this gist Dec 17, 2015. 1 changed file with 10 additions and 1 deletion.
    11 changes: 10 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -80,4 +80,13 @@ FROM
    stl_query q JOIN query_sql qs ON (q.query = qs.query)
    WHERE
    q.query = …
    ORDER BY starttime;
    ORDER BY starttime;

    -- Show the most recently executed DDL statements
    SELECT
    starttime,
    xid,
    LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql
    FROM stl_ddltext
    GROUP BY 1, 2
    ORDER BY 1 DESC;
  22. @iconara iconara revised this gist Dec 17, 2015. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -57,6 +57,7 @@ FROM STL_QUERYTEXT
    WHERE query = …;

    -- Get the full SQL, plus more query details from a query ID
    -- filter on xid to see all (including Redshift internal) operations in the transaction
    WITH query_sql AS (
    SELECT
    query,
    @@ -71,10 +72,12 @@ SELECT
    pid,
    starttime,
    endtime,
    DATEDIFF(milliseconds, starttime, endtime)/1000.0 AS duration,
    TRIM(database) AS database,
    (CASE aborted WHEN 1 THEN TRUE ELSE FALSE END) AS aborted,
    sql
    FROM
    stl_query q JOIN query_sql qs ON (q.query = qs.query)
    WHERE
    q.query = …;
    q.query = …
    ORDER BY starttime;
  23. @iconara iconara revised this gist Dec 17, 2015. 1 changed file with 24 additions and 1 deletion.
    25 changes: 24 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -54,4 +54,27 @@ SELECT TIMESTAMP 'epoch' + (millisecond_timestamp/1000 * INTERVAL '1 second') FR
    -- Get the full SQL from a query ID
    SELECT LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql
    FROM STL_QUERYTEXT
    WHERE query = …;
    WHERE query = …;

    -- Get the full SQL, plus more query details from a query ID
    WITH query_sql AS (
    SELECT
    query,
    LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql
    FROM stl_querytext
    GROUP BY 1
    )
    SELECT
    q.query,
    userid,
    xid,
    pid,
    starttime,
    endtime,
    TRIM(database) AS database,
    (CASE aborted WHEN 1 THEN TRUE ELSE FALSE END) AS aborted,
    sql
    FROM
    stl_query q JOIN query_sql qs ON (q.query = qs.query)
    WHERE
    q.query = …;
  24. @iconara iconara revised this gist Dec 17, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -53,5 +53,5 @@ SELECT TIMESTAMP 'epoch' + (millisecond_timestamp/1000 * INTERVAL '1 second') FR

    -- Get the full SQL from a query ID
    SELECT LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql
    FROM SVV_QUERY_INFLIGHT
    FROM STL_QUERYTEXT
    WHERE query = …;
  25. @iconara iconara revised this gist Dec 17, 2015. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -49,4 +49,9 @@ ORDER BY starttime DESC
    LIMIT 100;

    -- Convert a millisecond resolution number to a TIMESTAMP
    SELECT TIMESTAMP 'epoch' + (millisecond_timestamp/1000 * INTERVAL '1 second') FROM …;
    SELECT TIMESTAMP 'epoch' + (millisecond_timestamp/1000 * INTERVAL '1 second') FROM …;

    -- Get the full SQL from a query ID
    SELECT LISTAGG(text) WITHIN GROUP (ORDER BY sequence) AS sql
    FROM SVV_QUERY_INFLIGHT
    WHERE query = …;
  26. @iconara iconara revised this gist Nov 11, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -49,4 +49,4 @@ ORDER BY starttime DESC
    LIMIT 100;

    -- Convert a millisecond resolution number to a TIMESTAMP
    SELECT TIMESTAMP 'epoch' + (timestamp/1000 * INTERVAL '1 second') FROM foo;
    SELECT TIMESTAMP 'epoch' + (millisecond_timestamp/1000 * INTERVAL '1 second') FROM …;
  27. @iconara iconara revised this gist Nov 11, 2015. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -46,4 +46,7 @@ SELECT * FROM pg_database;
    SELECT *
    FROM stl_load_errors
    ORDER BY starttime DESC
    LIMIT 100;
    LIMIT 100;

    -- Convert a millisecond resolution number to a TIMESTAMP
    SELECT TIMESTAMP 'epoch' + (timestamp/1000 * INTERVAL '1 second') FROM foo;
  28. @iconara iconara revised this gist Nov 6, 2015. 1 changed file with 8 additions and 1 deletion.
    9 changes: 8 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -39,4 +39,11 @@ SELECT * FROM pg_user;
    SELECT * FROM pg_group;

    -- List all databases
    SELECT * FROM pg_database;
    SELECT * FROM pg_database;

    -- List the 100 last load errors
    -- see http://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html
    SELECT *
    FROM stl_load_errors
    ORDER BY starttime DESC
    LIMIT 100;
  29. @iconara iconara revised this gist Nov 4, 2015. 1 changed file with 4 additions and 1 deletion.
    5 changes: 4 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -36,4 +36,7 @@ ORDER BY 1, 2;

    -- List users and groups
    SELECT * FROM pg_user;
    SELECT * FROM pg_group;
    SELECT * FROM pg_group;

    -- List all databases
    SELECT * FROM pg_database;
  30. @iconara iconara revised this gist Nov 4, 2015. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion queries.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -32,4 +32,8 @@ FROM
    svv_diskusage.col = pg_attribute.attnum-1 AND
    svv_diskusage.tbl = pg_attribute.attrelid
    GROUP BY 1, 2
    ORDER BY 1, 2
    ORDER BY 1, 2;

    -- List users and groups
    SELECT * FROM pg_user;
    SELECT * FROM pg_group;