Skip to content

Instantly share code, notes, and snippets.

@jkatz
Last active May 7, 2021 16:28
Show Gist options
  • Save jkatz/de1b3c9c332a20d5c9ec3d1715c13e37 to your computer and use it in GitHub Desktop.
Save jkatz/de1b3c9c332a20d5c9ec3d1715c13e37 to your computer and use it in GitHub Desktop.
CREATE TABLE data (id int PRIMARY KEY);
INSERT INTO data SELECT * FROM generate_series(1,5000000);
----
EXPLAIN ANALYZE
WITH r AS (
SELECT (random() * (SELECT count(*) FROM data))::int x
)
SELECT data.*
FROM r
JOIN data ON id = x
LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------
----------------------------------------------------------------
Limit (cost=268045.17..268053.21 rows=1 width=4) (actual time=714.568..714.631 rows=1 loops=1)
CTE r
-> Result (cost=268044.60..268044.62 rows=1 width=4) (actual time=710.619..710.680 rows=1 lo
ops=1)
InitPlan 1 (returns $1)
-> Finalize Aggregate (cost=268044.59..268044.60 rows=1 width=8) (actual time=710.61
5..710.676 rows=1 loops=1)
-> Gather (cost=268044.37..268044.58 rows=2 width=8) (actual time=709.158..710
.669 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=267044.37..267044.38 rows=1 width=8) (actual
time=692.894..692.895 rows=1 loops=3)
-> Parallel Seq Scan on data data_1 (cost=0.00..261852.90 rows=207
6590 width=0) (actual time=5.715..606.674 rows=1666667 loops=3)
-> Nested Loop (cost=0.56..8.60 rows=1 width=4) (actual time=714.567..714.568 rows=1 loops=1)
-> CTE Scan on r (cost=0.00..0.02 rows=1 width=4) (actual time=710.621..710.621 rows=1 l
oops=1)
-> Index Only Scan using data_pkey on data (cost=0.56..8.57 rows=1 width=4) (actual time
=3.927..3.927 rows=1 loops=1)
Index Cond: (id = r.x)
Heap Fetches: 1
Planning Time: 0.131 ms
Execution Time: 714.665 ms
EXPLAIN ANALYZE
select * from data limit 1 offset (select (random() * count(*))::int from data);
QUERY PLAN
---------------------------------------------------------------------------------------------------
------------------------------------
Limit (cost=332477.25..332477.31 rows=1 width=4) (actual time=1244.749..1244.751 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Aggregate (cost=303384.69..303384.71 rows=1 width=4) (actual time=1239.555..1239.556 rows
=1 loops=1)
-> Seq Scan on data data_1 (cost=0.00..290925.15 rows=4983815 width=0) (actual time=0.
014..1073.745 rows=5000000 loops=1)
-> Seq Scan on data (cost=0.00..290925.15 rows=4983815 width=4) (actual time=0.006..4.044 rows
=59689 loops=1)
Planning Time: 0.788 ms
Execution Time: 1244.777 ms
(7 rows)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment