gistfile1.txt

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

gistfile2.txt

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)