Index Only Scan Query Plans
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