Skip to content

Instantly share code, notes, and snippets.

@githoov

githoov/benchmarks.sql

Last active May 21, 2016 16:54
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save githoov/0cfa012717d1998b5be4a4ecadbb6101 to your computer and use it in GitHub Desktop.
Save githoov/0cfa012717d1998b5be4a4ecadbb6101 to your computer and use it in GitHub Desktop.
Download ZIP
Redshift Benchmark Queries
Raw
benchmarks.sql
/*
${TABLE} is a placeholder for the table variants
you will be benchmarking with this script. Leave
${TABLE} in, as it will be used to dynamical substitute
your table names at the command line.
*/
\timing on
\o /dev/null
/* simple count: checks baseline performance and metadata querying */
select count(*) --1
from ${TABLE};
select count(*) --2
from ${TABLE};
select count(*) --3
from ${TABLE};
select count(*) --4
from ${TABLE};
select count(*) --5
from ${TABLE};
select count(*) --6
from ${TABLE};
select count(*) --7
from ${TABLE};
select count(*) --8
from ${TABLE};
select count(*) --9
from ${TABLE};
select count(*) --10
from ${TABLE};
select count(*) --11
from ${TABLE};
select count(*) --12
from ${TABLE};
select count(*) --13
from ${TABLE};
select count(*) --14
from ${TABLE};
select count(*) --15
from ${TABLE};
select count(*) --16
from ${TABLE};
/* simple count and group by: checks baseline performance and metadata querying */
select event_type, count(*) --1
from ${TABLE} group by 1;
select event_type, count(*) --2
from ${TABLE} group by 1;
select event_type, count(*) --3
from ${TABLE} group by 1;
select event_type, count(*) --4
from ${TABLE} group by 1;
select event_type, count(*) --5
from ${TABLE} group by 1;
select event_type, count(*) --6
from ${TABLE} group by 1;
select event_type, count(*) --7
from ${TABLE} group by 1;
select event_type, count(*) --8
from ${TABLE} group by 1;
select event_type, count(*) --9
from ${TABLE} group by 1;
select event_type, count(*) --10
from ${TABLE} group by 1;
select event_type, count(*) --11
from ${TABLE} group by 1;
select event_type, count(*) --12
from ${TABLE} group by 1;
select event_type, count(*) --13
from ${TABLE} group by 1;
select event_type, count(*) --14
from ${TABLE} group by 1;
select event_type, count(*) --15
from ${TABLE} group by 1;
select event_type, count(*) --16
from ${TABLE} group by 1;
/* simple count with date restriction: checks sortkey/filter performance */
select count(*) --1
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --2
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --3
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --4
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --5
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --6
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --7
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --8
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --9
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --10
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --11
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --12
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --13
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --14
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --15
from ${TABLE}
where created_at >= current_date - interval '90 day';
select count(*) --16
from ${TABLE}
where created_at >= current_date - interval '90 day';
/* simple count with join: checks join performance */
select count(*) --1
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --2
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --3
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --4
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --5
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --6
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --7
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --8
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --9
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --10
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --11
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --12
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --13
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --14
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --15
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
select count(*) --16
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug;
/* simple count and group by with join */
select ${JOIN_TABLE}.host_url --1
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --2
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --3
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --4
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --5
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --6
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --7
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --8
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --9
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --10
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --11
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --12
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --13
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --14
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --15
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
select ${JOIN_TABLE}.host_url --16
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
group by 1;
/* simple count and group by with join and where clause */
select ${JOIN_TABLE}.host_url --1
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --2
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --3
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --4
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --5
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --6
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --7
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --8
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --9
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --10
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --11
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --12
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --13
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --14
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --15
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
select ${JOIN_TABLE}.host_url --16
, count(*)
from ${TABLE}
left join ${JOIN_TABLE}
on ${TABLE}.instance_slug = ${JOIN_TABLE}.slug
where ${TABLE}.created_at >= current_date - interval '90 day'
group by 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment