Skip to content

Instantly share code, notes, and snippets.

@just3ws

just3ws/all.sql

Last active April 12, 2017 14:34
Show Gist options
  • Save just3ws/3e940ae77705521721b9b43978c24caa to your computer and use it in GitHub Desktop.
Save just3ws/3e940ae77705521721b9b43978c24caa to your computer and use it in GitHub Desktop.
Download ZIP
Refactoring Postgres Queries
Raw
all.sql
explain (analyze on, buffers on, verbose on, timing on)
select l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
from simulation_results l
inner join (
select simulation_id, max(created_at) created_at
from simulation_results
where user_id = 827911
group by simulation_id
) r
on l.simulation_id = r.simulation_id and l.created_at = r.created_at
where l.user_id = 827911;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1.30..1.32 rows=1 width=52) (actual time=0.045..0.063 rows=3 loops=1)
Output: (max(created_at) OVER (?)), id, simulation_id, user_id, content_package_id, score, result, total_time_taken, updated_at, created_at
Buffers: shared hit=1
-> WindowAgg (cost=1.30..1.32 rows=1 width=52) (actual time=0.044..0.058 rows=23 loops=1)
Output: max(created_at) OVER (?), id, simulation_id, user_id, content_package_id, score, result, total_time_taken, updated_at, created_at
Buffers: shared hit=1
-> Sort (cost=1.30..1.30 rows=1 width=44) (actual time=0.040..0.043 rows=23 loops=1)
Output: simulation_id, created_at, id, user_id, content_package_id, score, result, total_time_taken, updated_at
Sort Key: simulation_results.simulation_id, simulation_results.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=1
-> Seq Scan on public.simulation_results (cost=0.00..1.29 rows=1 width=44) (actual time=0.013..0.024 rows=23 loops=1)
Output: simulation_id, created_at, id, user_id, content_package_id, score, result, total_time_taken, updated_at
Filter: (simulation_results.user_id = 827911)
Buffers: shared hit=1
Planning time: 0.096 ms
Execution time: 0.092 ms
(17 rows)
--
explain (analyze on, buffers on, verbose on, timing on)
select l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
from simulation_results l
inner join (
select simulation_id, max(created_at) created_at
from simulation_results
where user_id = 827911
group by simulation_id
) r
using (simulation_id, created_at)
where l.user_id = 827911;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.29..2.62 rows=1 width=44) (actual time=0.048..0.052 rows=3 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Join Filter: ((l.simulation_id = simulation_results.simulation_id) AND (l.created_at = (max(simulation_results.created_at))))
Rows Removed by Join Filter: 66
Buffers: shared hit=2
-> Seq Scan on public.simulation_results l (cost=0.00..1.29 rows=1 width=44) (actual time=0.009..0.013 rows=23 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Filter: (l.user_id = 827911)
Buffers: shared hit=1
-> HashAggregate (cost=1.29..1.30 rows=1 width=12) (actual time=0.001..0.001 rows=3 loops=23)
Output: simulation_results.simulation_id, max(simulation_results.created_at)
Group Key: simulation_results.simulation_id
Buffers: shared hit=1
-> Seq Scan on public.simulation_results (cost=0.00..1.29 rows=1 width=12) (actual time=0.002..0.006 rows=23 loops=1)
Output: simulation_results.id, simulation_results.simulation_id, simulation_results.user_id, simulation_results.content_package_id, simulation_results.score, simulation_results.result, simulation_results.total_time_taken, simulation_results.created_at, simulation_results.updated_at
Filter: (simulation_results.user_id = 827911)
Buffers: shared hit=1
Planning time: 1.797 ms
Execution time: 0.086 ms
(19 rows)
--
explain (analyze on, buffers on, verbose on, timing on)
select l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
from simulation_results l
inner join (
select
simulation_id,
max(created_at) over (partition by simulation_id) created_at
from simulation_results
where user_id = 827911
group by simulation_id, created_at
) r
using (simulation_id, created_at)
where l.user_id = 827911;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.30..2.63 rows=1 width=44) (actual time=0.412..0.464 rows=23 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Join Filter: ((l.simulation_id = simulation_results.simulation_id) AND (l.created_at = (max(simulation_results.created_at) OVER (?))))
Rows Removed by Join Filter: 506
Buffers: shared hit=2
-> Seq Scan on public.simulation_results l (cost=0.00..1.29 rows=1 width=44) (actual time=0.007..0.009 rows=23 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Filter: (l.user_id = 827911)
Buffers: shared hit=1
-> WindowAgg (cost=1.30..1.32 rows=1 width=20) (actual time=0.007..0.017 rows=23 loops=23)
Output: simulation_results.simulation_id, max(simulation_results.created_at) OVER (?), simulation_results.created_at
Buffers: shared hit=1
-> Group (cost=1.30..1.31 rows=1 width=12) (actual time=0.001..0.008 rows=23 loops=23)
Output: simulation_results.simulation_id, simulation_results.created_at
Group Key: simulation_results.simulation_id, simulation_results.created_at
Buffers: shared hit=1
-> Sort (cost=1.30..1.30 rows=1 width=12) (actual time=0.001..0.003 rows=23 loops=23)
Output: simulation_results.simulation_id, simulation_results.created_at
Sort Key: simulation_results.simulation_id, simulation_results.created_at
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=1
-> Seq Scan on public.simulation_results (cost=0.00..1.29 rows=1 width=12) (actual time=0.002..0.006 rows=23 loops=1)
Output: simulation_results.simulation_id, simulation_results.created_at
Filter: (simulation_results.user_id = 827911)
Buffers: shared hit=1
Planning time: 0.179 ms
Execution time: 0.503 ms
(27 rows)
--
explain (analyze on, buffers on, verbose on, timing on)
select l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
from simulation_results l
inner join (
select simulation_id, max(created_at) created_at
from simulation_results
where user_id = 827911
group by simulation_id
) r
on l.simulation_id = r.simulation_id and l.created_at = r.created_at
where l.user_id = 827911;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.29..2.62 rows=1 width=44) (actual time=0.046..0.049 rows=3 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Join Filter: ((l.simulation_id = simulation_results.simulation_id) AND (l.created_at = (max(simulation_results.created_at))))
Rows Removed by Join Filter: 66
Buffers: shared hit=2
-> Seq Scan on public.simulation_results l (cost=0.00..1.29 rows=1 width=44) (actual time=0.009..0.010 rows=23 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Filter: (l.user_id = 827911)
Buffers: shared hit=1
-> HashAggregate (cost=1.29..1.30 rows=1 width=12) (actual time=0.001..0.001 rows=3 loops=23)
Output: simulation_results.simulation_id, max(simulation_results.created_at)
Group Key: simulation_results.simulation_id
Buffers: shared hit=1
-> Seq Scan on public.simulation_results (cost=0.00..1.29 rows=1 width=12) (actual time=0.001..0.006 rows=23 loops=1)
Output: simulation_results.id, simulation_results.simulation_id, simulation_results.user_id, simulation_results.content_package_id, simulation_results.score, simulation_results.result, simulation_results.total_time_taken, simulation_results.created_at, simulation_results.updated_at
Filter: (simulation_results.user_id = 827911)
Buffers: shared hit=1
Planning time: 0.159 ms
Execution time: 0.077 ms
(19 rows)
--
explain (analyze on, buffers on, verbose on, timing on)
select l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
from simulation_results l
natural join (
select simulation_id, max(created_at) created_at
from simulation_results
where user_id = 827911
group by simulation_id
) r
where l.user_id = 827911;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=1.29..2.62 rows=1 width=44) (actual time=0.050..0.053 rows=3 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Join Filter: ((l.simulation_id = simulation_results.simulation_id) AND (l.created_at = (max(simulation_results.created_at))))
Rows Removed by Join Filter: 66
Buffers: shared hit=2
-> Seq Scan on public.simulation_results l (cost=0.00..1.29 rows=1 width=44) (actual time=0.010..0.014 rows=23 loops=1)
Output: l.id, l.simulation_id, l.user_id, l.content_package_id, l.score, l.result, l.total_time_taken, l.created_at, l.updated_at
Filter: (l.user_id = 827911)
Buffers: shared hit=1
-> HashAggregate (cost=1.29..1.30 rows=1 width=12) (actual time=0.001..0.001 rows=3 loops=23)
Output: simulation_results.simulation_id, max(simulation_results.created_at)
Group Key: simulation_results.simulation_id
Buffers: shared hit=1
-> Seq Scan on public.simulation_results (cost=0.00..1.29 rows=1 width=12) (actual time=0.001..0.005 rows=23 loops=1)
Output: simulation_results.id, simulation_results.simulation_id, simulation_results.user_id, simulation_results.content_package_id, simulation_results.score, simulation_results.result, simulation_results.total_time_taken, simulation_results.created_at, simulation_results.updated_at
Filter: (simulation_results.user_id = 827911)
Buffers: shared hit=1
Planning time: 0.178 ms
Execution time: 0.098 ms
(19 rows)
--
explain (analyze on, buffers on, verbose on, timing on)
select
distinct on (simulation_id, user_id) max(created_at) over (partition by simulation_id order by created_at desc) created_at,
id, simulation_id, user_id, content_package_id, score, result, total_time_taken, updated_at
from simulation_results
where user_id = 827911;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=1.30..1.32 rows=1 width=52) (actual time=0.032..0.050 rows=3 loops=1)
Output: (max(created_at) OVER (?)), id, simulation_id, user_id, content_package_id, score, result, total_time_taken, updated_at, created_at
Buffers: shared hit=1
-> WindowAgg (cost=1.30..1.32 rows=1 width=52) (actual time=0.032..0.047 rows=23 loops=1)
Output: max(created_at) OVER (?), id, simulation_id, user_id, content_package_id, score, result, total_time_taken, updated_at, created_at
Buffers: shared hit=1
-> Sort (cost=1.30..1.30 rows=1 width=44) (actual time=0.027..0.029 rows=23 loops=1)
Output: simulation_id, created_at, id, user_id, content_package_id, score, result, total_time_taken, updated_at
Sort Key: simulation_results.simulation_id, simulation_results.created_at DESC
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=1
-> Seq Scan on public.simulation_results (cost=0.00..1.29 rows=1 width=44) (actual time=0.009..0.013 rows=23 loops=1)
Output: simulation_id, created_at, id, user_id, content_package_id, score, result, total_time_taken, updated_at
Filter: (simulation_results.user_id = 827911)
Buffers: shared hit=1
Planning time: 0.085 ms
Execution time: 0.076 ms
(17 rows)
Raw
data.txt
id | simulation_id | user_id | content_package_id | score | result | total_time_taken | created_at | updated_at
----+---------------+---------+--------------------+-------+--------+------------------+----------------------------+----------------------------
1 | 1 | 827911 | 1099 | 10 | 1 | 300 | 2017-04-10 15:47:58.383046 | 2017-04-10 15:47:58.383046
2 | 1 | 827911 | 1099 | 99 | 1 | 847 | 2017-04-11 21:37:59.559099 | 2017-04-11 21:37:59.559099
3 | 1 | 827911 | 1099 | 77 | 0 | 103 | 2017-04-11 21:38:34.925895 | 2017-04-11 21:38:34.925895
4 | 1 | 827911 | 1099 | 31 | 0 | 143 | 2017-04-11 21:38:40.246775 | 2017-04-11 21:38:40.246775
5 | 1 | 827911 | 1099 | 53 | 0 | 153 | 2017-04-11 21:38:41.696413 | 2017-04-11 21:38:41.696413
6 | 2 | 827911 | 1099 | 22 | 0 | 110 | 2017-04-11 21:39:18.465974 | 2017-04-11 21:39:18.465974
7 | 2 | 827911 | 1099 | 77 | 1 | 103 | 2017-04-11 21:39:20.141829 | 2017-04-11 21:39:20.141829
8 | 2 | 827911 | 1099 | 22 | 0 | 150 | 2017-04-11 21:39:21.619674 | 2017-04-11 21:39:21.619674
9 | 2 | 827911 | 1099 | 60 | 0 | 197 | 2017-04-11 21:39:22.515027 | 2017-04-11 21:39:22.515027
10 | 3 | 827911 | 1099 | 82 | 0 | 196 | 2017-04-11 21:39:23.290977 | 2017-04-11 21:39:23.290977
11 | 3 | 827911 | 1099 | 36 | 1 | 124 | 2017-04-11 21:39:24.123781 | 2017-04-11 21:39:24.123781
12 | 2 | 827911 | 1099 | 35 | 1 | 120 | 2017-04-11 21:39:25.082835 | 2017-04-11 21:39:25.082835
13 | 1 | 827911 | 1099 | 46 | 1 | 142 | 2017-04-11 21:39:25.945943 | 2017-04-11 21:39:25.945943
14 | 1 | 827911 | 1099 | 93 | 1 | 138 | 2017-04-11 21:39:34.295074 | 2017-04-11 21:39:34.295074
15 | 2 | 827911 | 1099 | 47 | 0 | 188 | 2017-04-11 21:39:34.304328 | 2017-04-11 21:39:34.304328
16 | 3 | 827911 | 1099 | 29 | 0 | 198 | 2017-04-11 21:39:34.312877 | 2017-04-11 21:39:34.312877
17 | 1 | 827911 | 1099 | 11 | 0 | 131 | 2017-04-11 21:39:34.320998 | 2017-04-11 21:39:34.320998
18 | 1 | 827911 | 1099 | 54 | 1 | 184 | 2017-04-11 21:39:34.328954 | 2017-04-11 21:39:34.328954
19 | 2 | 827911 | 1099 | 99 | 0 | 165 | 2017-04-11 21:39:34.338804 | 2017-04-11 21:39:34.338804
20 | 2 | 827911 | 1099 | 61 | 0 | 137 | 2017-04-11 21:39:34.349267 | 2017-04-11 21:39:34.349267
21 | 1 | 827911 | 1099 | 48 | 0 | 180 | 2017-04-11 21:39:34.360706 | 2017-04-11 21:39:34.360706
22 | 3 | 827911 | 1099 | 34 | 1 | 188 | 2017-04-11 21:39:34.369128 | 2017-04-11 21:39:34.369128
23 | 2 | 827911 | 1099 | 96 | 1 | 168 | 2017-04-11 21:39:34.387152 | 2017-04-11 21:39:34.387152
(23 rows)
Raw
expected_results-unsorted.txt
id | simulation_id | user_id | content_package_id | score | result | total_time_taken | created_at | updated_at
----+---------------+---------+--------------------+-------+--------+------------------+----------------------------+----------------------------
23 | 2 | 827911 | 1099 | 96 | 1 | 168 | 2017-04-11 21:39:34.387152 | 2017-04-11 21:39:34.387152
22 | 3 | 827911 | 1099 | 34 | 1 | 188 | 2017-04-11 21:39:34.369128 | 2017-04-11 21:39:34.369128
21 | 1 | 827911 | 1099 | 48 | 0 | 180 | 2017-04-11 21:39:34.360706 | 2017-04-11 21:39:34.360706
@just3ws
Copy link
Author

just3ws commented Apr 12, 2017

Goal of the queries was find the most recent records for each simulation_id.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment