nord vpnnord vpn
Ad

Index Only Scan Query Plans

mail@pastecode.io avatar
unknown
pgsql
a year ago
2.0 kB
1
Indexable
Never
--Create Tables
CREATE TABLE t1 (a INT, b CHAR(100) DEFAULT 'abcd');
CREATE TABLE t2 (a INT, b CHAR(100) DEFAULT 'abcd');

-- Insert data into tables
INSERT INTO t1 SELECT * FROM generate_series(1, 50000000);
INSERT INTO t2 SELECT a FROM t1 ORDER BY random();

-- Create Indexes
CREATE INDEX ON t1(a);
CREATE INDEX ON t2(a);

-- Vacuum analyze
VACUUM ANALYZE

--Queries

-- Count all rows in t1
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, COSTS OFF) SELECT COUNT(a) FROM t1;

                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=136697
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=136697
         ->  Partial Aggregate (actual rows=1 loops=3)
               Buffers: shared hit=136697
               ->  Parallel Index Only Scan using t1_a_idx on t1 (actual rows=16666667 loops=3)
                     Heap Fetches: 0
                     Buffers: shared hit=136697
 Planning Time: 0.297 ms
 Execution Time: 1593.595 ms


-- Count all rows in t2
EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, COSTS OFF) SELECT COUNT(a) FROM t2;

                                           QUERY PLAN                                           
------------------------------------------------------------------------------------------------
 Finalize Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=48256559
   ->  Gather (actual rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=48256559
         ->  Partial Aggregate (actual rows=1 loops=3)
               Buffers: shared hit=48256559
               ->  Parallel Index Only Scan using t2_a_idx on t2 (actual rows=16666667 loops=3)
                     Heap Fetches: 0
                     Buffers: shared hit=48256559
 Planning Time: 0.182 ms
 Execution Time: 5079.874 ms

nord vpnnord vpn
Ad