Return to Question

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=64804990.92..65623829.48 rows=327535424 width=4) (actual time=2673682.054..3071428.236 rows=327535416 loops=1)
   Output: customer_id
   Sort Key: service.customer_id
   Sort Method: external merge  Disk: 4482448kB
   ->  Seq Scan on public.service  (cost=0.00..5045816.24 rows=327535424 width=4) (actual time=0.029..129335.120 rows=327535416 loops=1)
         Output: customer_id
 Planning time: 0.178 ms
 Execution time: 3086375.395 ms
(8 rows)

Time: 3086381.530 ms

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=0.043..987310.506 rows=327535416 loops=1)
   Output: customer_id
   Heap Fetches: 327535416
 Planning time: 0.127 ms
 Execution time: 1003076.590 ms
(5 rows)

Time: 1003082.344 ms

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                               QUERY PLAN                                                               
----------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=64804990.92..65623829.48 rows=327535424 width=4) (actual time=487209.250..546557.064 rows=327535416 loops=1)
   Output: customer_id
   Sort Key: service.customer_id
   Sort Method: external merge  Disk: 4482448kB
   ->  Seq Scan on public.service  (cost=0.00..5045816.24 rows=327535424 width=4) (actual time=2.705..49659.173 rows=327535416 loops=1)
         Output: customer_id
 Planning time: 0.093 ms
 Execution time: 554914.731 ms
(8 rows)

Time: 554919.649 ms

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=2.753..346400.896 rows=327535416 loops=1)
   Output: customer_id
   Heap Fetches: 327535416
 Planning time: 1.921 ms
 Execution time: 355637.985 ms
(5 rows)

Time: 355647.367 ms

Here's the execution with seqscan turned off:

# explain (analyze,verbose) select customer_id from service order by customer_id;
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using service_customer_id_idx on public.service  (cost=0.57..1061141647.19 rows=327535424 width=4) (actual time=0.043..987310.506 rows=327535416 loops=1)
   Output: customer_id
   Heap Fetches: 327535416
 Planning time: 0.127 ms
 Execution time: 1003076.590 ms
(5 rows)

Time: 1003082.344 ms