Skip to content

Instantly share code, notes, and snippets.

@baryluk

baryluk/gist:ec0484295444c6bca9337c88b9739d86

Last active March 19, 2019 23:51
Show Gist options
  • Save baryluk/ec0484295444c6bca9337c88b9739d86 to your computer and use it in GitHub Desktop.
Save baryluk/ec0484295444c6bca9337c88b9739d86 to your computer and use it in GitHub Desktop.
Download ZIP
ordered vs unordered dequeue from the queue
Raw
gistfile1.txt
Time: 3.133 ms
[local] baryluk@d=> EXPLAIN ANALYZE SELECT itemid FROM queue LIMIT 1;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.00..0.01 rows=1 width=4) (actual time=0.040..0.041 rows=1 loops=1) │
│ -> Seq Scan on queue (cost=0.00..1680786.04 rows=116492104 width=4) (actual time=0.039..0.039 rows=1 loops=1) │
│ Planning Time: 0.046 ms │
│ Execution Time: 0.050 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(4 rows)
Time: 0.309 ms
[local] baryluk@d=> EXPLAIN ANALYZE SELECT itemid FROM queue ORDER BY itemid LIMIT 1;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Limit (cost=0.57..2.35 rows=1 width=4) (actual time=2.737..2.737 rows=1 loops=1) │
│ -> Index Only Scan using queue_pkey on queue (cost=0.57..207424744.21 rows=116492104 width=4) (actual time=2.736..2.736 rows=1 loops=1) │
│ Heap Fetches: 2 │
│ Planning Time: 0.049 ms │
│ Execution Time: 2.747 ms │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)
Time: 3.071 ms
Actual queries that are being executed, involving subqueries above:
[local] baryluk@d=> EXPLAIN ANALYZE DELETE FROM queue
WHERE itemid = (
SELECT itemid
FROM queue
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING itemid;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Delete on queue (cost=0.59..8.61 rows=1 width=6) (actual time=0.091..0.092 rows=1 loops=1) │
│ InitPlan 1 (returns $1) │
│ -> Limit (cost=0.00..0.02 rows=1 width=10) (actual time=0.047..0.048 rows=1 loops=1) │
│ -> LockRows (cost=0.00..2845707.08 rows=116492104 width=10) (actual time=0.047..0.047 rows=1 loops=1) │
│ -> Seq Scan on queue queue_1 (cost=0.00..1680786.04 rows=116492104 width=10) (actual time=0.038..0.038 rows=1 loops=1) │
│ -> Index Scan using queue_pkey on queue (cost=0.57..8.59 rows=1 width=6) (actual time=0.084..0.085 rows=1 loops=1) │
│ Index Cond: (itemid = $1) │
│ Planning Time: 0.074 ms │
│ Execution Time: 0.114 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
Time: 0.487 ms
[local] baryluk@d=> EXPLAIN ANALYZE DELETE FROM queue
WHERE itemid = (
SELECT itemid
FROM queue
ORDER BY itemid
FOR UPDATE SKIP LOCKED
LIMIT 1
)
RETURNING itemid;
┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Delete on queue (cost=2.93..10.95 rows=1 width=6) (actual time=2.742..2.742 rows=1 loops=1) │
│ InitPlan 1 (returns $2) │
│ -> Limit (cost=0.57..2.36 rows=1 width=10) (actual time=2.726..2.726 rows=1 loops=1) │
│ -> LockRows (cost=0.57..208908121.62 rows=116492104 width=10) (actual time=2.725..2.725 rows=1 loops=1) │
│ -> Index Scan using queue_pkey on queue queue_1 (cost=0.57..207743200.58 rows=116492104 width=10) (actual time=2.715..2.716 rows=1 loops=1) │
│ -> Index Scan using queue_pkey on queue (cost=0.57..8.59 rows=1 width=6) (actual time=2.736..2.737 rows=1 loops=1) │
│ Index Cond: (itemid = $2) │
│ Planning Time: 0.080 ms │
│ Execution Time: 2.764 ms │
└───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(9 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment