Skip to content

Instantly share code, notes, and snippets.

@jfischoff

jfischoff/partition query and plan

Created June 21, 2020 17:29
Show Gist options
  • Save jfischoff/c827ad612239214b9eaf56bd2a2a0cca to your computer and use it in GitHub Desktop.
Save jfischoff/c827ad612239214b9eaf56bd2a2a0cca to your computer and use it in GitHub Desktop.
Download ZIP
Raw
partition query and plan
postgres=# explain (analyze, buffers) UPDATE payloads
postgres-# SET state='dequeued'
postgres-# WHERE id in
postgres-# ( SELECT p1.id
postgres(# FROM payloads AS p1
postgres(# WHERE p1.state='enqueued'
postgres(# ORDER BY p1.modified_at ASC
postgres(# FOR UPDATE SKIP LOCKED
postgres(# LIMIT 1
postgres(# )
postgres-# RETURNING id, state, attempts, modified_at, value
postgres-# ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on payloads (cost=8.94..95.83 rows=3625 width=66) (actual time=0.095..0.095 rows=0 loops=1)
Update on payloads_0_99999
Update on payloads_100000_199999
Update on payloads_200000_299999
Update on payloads_300000_399999
Update on payloads_400000_499999
Buffers: shared hit=25
-> Nested Loop (cost=8.94..19.17 rows=725 width=66) (actual time=0.022..0.022 rows=0 loops=1)
Buffers: shared hit=5
-> HashAggregate (cost=4.74..4.75 rows=1 width=40) (actual time=0.021..0.021 rows=0 loops=1)
Group Key: "ANY_subquery".id
Buffers: shared hit=5
-> Subquery Scan on "ANY_subquery" (cost=0.68..4.73 rows=1 width=40) (actual time=0.020..0.020 rows=0 loops=1)
Buffers: shared hit=5
-> Limit (cost=0.68..4.72 rows=1 width=26) (actual time=0.020..0.020 rows=0 loops=1)
Buffers: shared hit=5
-> LockRows (cost=0.68..142.08 rows=35 width=26) (actual time=0.019..0.020 rows=0 loops=1)
Buffers: shared hit=5
-> Merge Append (cost=0.68..141.73 rows=35 width=26) (actual time=0.018..0.018 rows=0 loops=1)
Sort Key: p1.modified_at
Buffers: shared hit=5
-> Index Scan using payloads_0_99999_modified_at_idx on payloads_0_99999 p1 (cost=0.12..28.22 rows=7 width=26) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_100000_199999_modified_at_idx on payloads_100000_199999 p1_1 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.004 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_200000_299999_modified_at_idx on payloads_200000_299999 p1_2 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_300000_399999_modified_at_idx on payloads_300000_399999 p1_3 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_400000_499999_modified_at_idx on payloads_400000_499999 p1_4 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Bitmap Heap Scan on payloads_0_99999 (cost=4.21..14.35 rows=7 width=30) (never executed)
Recheck Cond: (id = "ANY_subquery".id)
-> Bitmap Index Scan on payloads_0_99999_pkey (cost=0.00..4.21 rows=7 width=0) (never executed)
Index Cond: (id = "ANY_subquery".id)
-> Nested Loop (cost=8.94..19.17 rows=725 width=66) (actual time=0.018..0.018 rows=0 loops=1)
Buffers: shared hit=5
-> HashAggregate (cost=4.74..4.75 rows=1 width=40) (actual time=0.017..0.017 rows=0 loops=1)
Group Key: "ANY_subquery_1".id
Buffers: shared hit=5
-> Subquery Scan on "ANY_subquery_1" (cost=0.68..4.73 rows=1 width=40) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> Limit (cost=0.68..4.72 rows=1 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> LockRows (cost=0.68..142.08 rows=35 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> Merge Append (cost=0.68..141.73 rows=35 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Sort Key: p1_5.modified_at
Buffers: shared hit=5
-> Index Scan using payloads_0_99999_modified_at_idx on payloads_0_99999 p1_5 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_100000_199999_modified_at_idx on payloads_100000_199999 p1_6 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_200000_299999_modified_at_idx on payloads_200000_299999 p1_7 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_300000_399999_modified_at_idx on payloads_300000_399999 p1_8 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_400000_499999_modified_at_idx on payloads_400000_499999 p1_9 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Bitmap Heap Scan on payloads_100000_199999 (cost=4.21..14.35 rows=7 width=30) (never executed)
Recheck Cond: (id = "ANY_subquery_1".id)
-> Bitmap Index Scan on payloads_100000_199999_pkey (cost=0.00..4.21 rows=7 width=0) (never executed)
Index Cond: (id = "ANY_subquery_1".id)
-> Nested Loop (cost=8.94..19.17 rows=725 width=66) (actual time=0.019..0.019 rows=0 loops=1)
Buffers: shared hit=5
-> HashAggregate (cost=4.74..4.75 rows=1 width=40) (actual time=0.019..0.019 rows=0 loops=1)
Group Key: "ANY_subquery_2".id
Buffers: shared hit=5
-> Subquery Scan on "ANY_subquery_2" (cost=0.68..4.73 rows=1 width=40) (actual time=0.019..0.019 rows=0 loops=1)
Buffers: shared hit=5
-> Limit (cost=0.68..4.72 rows=1 width=26) (actual time=0.018..0.018 rows=0 loops=1)
Buffers: shared hit=5
-> LockRows (cost=0.68..142.08 rows=35 width=26) (actual time=0.018..0.018 rows=0 loops=1)
Buffers: shared hit=5
-> Merge Append (cost=0.68..141.73 rows=35 width=26) (actual time=0.018..0.018 rows=0 loops=1)
Sort Key: p1_10.modified_at
Buffers: shared hit=5
-> Index Scan using payloads_0_99999_modified_at_idx on payloads_0_99999 p1_10 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_100000_199999_modified_at_idx on payloads_100000_199999 p1_11 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_200000_299999_modified_at_idx on payloads_200000_299999 p1_12 (cost=0.12..28.22 rows=7 width=26) (actual time=0.005..0.005 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_300000_399999_modified_at_idx on payloads_300000_399999 p1_13 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_400000_499999_modified_at_idx on payloads_400000_499999 p1_14 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Bitmap Heap Scan on payloads_200000_299999 (cost=4.21..14.35 rows=7 width=30) (never executed)
Recheck Cond: (id = "ANY_subquery_2".id)
-> Bitmap Index Scan on payloads_200000_299999_pkey (cost=0.00..4.21 rows=7 width=0) (never executed)
Index Cond: (id = "ANY_subquery_2".id)
-> Nested Loop (cost=8.94..19.17 rows=725 width=66) (actual time=0.018..0.018 rows=0 loops=1)
Buffers: shared hit=5
-> HashAggregate (cost=4.74..4.75 rows=1 width=40) (actual time=0.018..0.018 rows=0 loops=1)
Group Key: "ANY_subquery_3".id
Buffers: shared hit=5
-> Subquery Scan on "ANY_subquery_3" (cost=0.68..4.73 rows=1 width=40) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> Limit (cost=0.68..4.72 rows=1 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> LockRows (cost=0.68..142.08 rows=35 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> Merge Append (cost=0.68..141.73 rows=35 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Sort Key: p1_15.modified_at
Buffers: shared hit=5
-> Index Scan using payloads_0_99999_modified_at_idx on payloads_0_99999 p1_15 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_100000_199999_modified_at_idx on payloads_100000_199999 p1_16 (cost=0.12..28.22 rows=7 width=26) (actual time=0.004..0.004 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_200000_299999_modified_at_idx on payloads_200000_299999 p1_17 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_300000_399999_modified_at_idx on payloads_300000_399999 p1_18 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_400000_499999_modified_at_idx on payloads_400000_499999 p1_19 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Bitmap Heap Scan on payloads_300000_399999 (cost=4.21..14.35 rows=7 width=30) (never executed)
Recheck Cond: (id = "ANY_subquery_3".id)
-> Bitmap Index Scan on payloads_300000_399999_pkey (cost=0.00..4.21 rows=7 width=0) (never executed)
Index Cond: (id = "ANY_subquery_3".id)
-> Nested Loop (cost=8.94..19.17 rows=725 width=66) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> HashAggregate (cost=4.74..4.75 rows=1 width=40) (actual time=0.017..0.017 rows=0 loops=1)
Group Key: "ANY_subquery_4".id
Buffers: shared hit=5
-> Subquery Scan on "ANY_subquery_4" (cost=0.68..4.73 rows=1 width=40) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> Limit (cost=0.68..4.72 rows=1 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> LockRows (cost=0.68..142.08 rows=35 width=26) (actual time=0.017..0.017 rows=0 loops=1)
Buffers: shared hit=5
-> Merge Append (cost=0.68..141.73 rows=35 width=26) (actual time=0.016..0.016 rows=0 loops=1)
Sort Key: p1_20.modified_at
Buffers: shared hit=5
-> Index Scan using payloads_0_99999_modified_at_idx on payloads_0_99999 p1_20 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_100000_199999_modified_at_idx on payloads_100000_199999 p1_21 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_200000_299999_modified_at_idx on payloads_200000_299999 p1_22 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.004 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_300000_399999_modified_at_idx on payloads_300000_399999 p1_23 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Index Scan using payloads_400000_499999_modified_at_idx on payloads_400000_499999 p1_24 (cost=0.12..28.22 rows=7 width=26) (actual time=0.003..0.003 rows=0 loops=1)
Filter: (state = 'enqueued'::state_t)
Buffers: shared hit=1
-> Bitmap Heap Scan on payloads_400000_499999 (cost=4.21..14.35 rows=7 width=30) (never executed)
Recheck Cond: (id = "ANY_subquery_4".id)
-> Bitmap Index Scan on payloads_400000_499999_pkey (cost=0.00..4.21 rows=7 width=0) (never executed)
Index Cond: (id = "ANY_subquery_4".id)
Planning Time: 8.292 ms
Execution Time: 1.041 ms
(174 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment