Postgres order uses indexes incorrectly

Question

Well, there are questions that say postgres doesn't use order, but my case is where it uses it incorrectly.

Sorting without an index - a hot start is cached after the results. Makes 8.48 seconds

explain (analyze,buffers) select * from users order by userid limit 100000;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=246372.98..246622.98 rows=100000 width=72) (actual time=8451.119..8479.138 rows=100000 loops=1)
   Buffers: shared hit=16134 read=35121
   ->  Sort  (cost=246372.98..251348.03 rows=1990021 width=72) (actual time=8451.117..8467.403 rows=100000 loops=1)
         Sort Key: userid
         Sort Method: top-N heapsort  Memory: 20207kB
         Buffers: shared hit=16134 read=35121
         ->  Seq Scan on users  (cost=0.00..71155.21 rows=1990021 width=72) (actual time=25.448..7782.830 rows=1995958 loops=1)
               Buffers: shared hit=16134 read=35121
 Planning time: 40.542 ms
 Execution time: 8487.556 ms
(10 rows)

Sort with an index in the userid column. Uses more disk I / O and takes a whopping 6.2 Mins

explain (analyze,buffers) select * from users order by userid limit 100000;
                                                                     QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..12771.83 rows=100000 width=72) (actual time=35.498..372437.748 rows=100000 loops=1)
   Buffers: shared hit=60846 read=39425
   ->  Index Scan using users_userid_idx on users  (cost=0.43..255288.96 rows=1998907 width=72) (actual time=35.496..372372.192 rows=100000 loops=1)
         Buffers: shared hit=60846 read=39425
 Planning time: 0.160 ms
 Execution time: 372476.536 ms
(6 rows)

Few notes

• I performed a Vacuum analysis before both queries were launched.
• Both were hot starts, and I took them after starting 3-4 times
• Mem was enough work, it uses the top N heapsort. Although the problem is sorting without an index, it is faster.

, , , , . Mac OSx postgres 9.4. , , .

- , , - .

Answer 1

, . , .

• Mac
• 256 ( 128 )
• postgres

, , .

explain (analyze,buffers) select * from users order by userid limit 100000;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..12788.49 rows=100000 width=72) (actual time=0.031..78.785 rows=100000 loops=1)
   Buffers: shared hit=100271
   ->  Index Scan using users_userid_idx on users  (cost=0.43..255244.73 rows=1995958 width=72) (actual time=0.030..65.937 rows=100000 loops=1)
         Buffers: shared hit=100271
 Planning time: 0.119 ms
 Execution time: 84.985 ms
(6 rows)

- - , , - . .

N .

                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=246955.09..247205.09 rows=100000 width=72) (actual time=707.350..734.954 rows=100000 loops=1)
   Buffers: shared hit=26071 read=25184
   ->  Sort  (cost=246955.09..251944.99 rows=1995958 width=72) (actual time=707.348..723.127 rows=100000 loops=1)
         Sort Key: userid
         Sort Method: top-N heapsort  Memory: 20207kB
         Buffers: shared hit=26071 read=25184
         ->  Seq Scan on users  (cost=0.00..71214.58 rows=1995958 width=72) (actual time=9.922..270.684 rows=1995958 loops=1)
               Buffers: shared hit=26071 read=25184
 Planning time: 0.090 ms
 Execution time: 743.788 ms
(10 rows)

128 , .

explain (analyze,buffers) select * from users order by userid limit 100000;
                                                                   QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..12788.49 rows=100000 width=72) (actual time=0.098..232.314 rows=100000 loops=1)
   Buffers: shared hit=61313 read=38958
   ->  Index Scan using users_userid_idx on users  (cost=0.43..255244.73 rows=1995958 width=72) (actual time=0.096..218.272 rows=100000 loops=1)
         Buffers: shared hit=61313 read=38958
 Planning time: 0.131 ms
 Execution time: 238.861 ms
(6 rows)


explain (analyze,buffers) select * from users order by userid limit 100000;
                                                          QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=246955.09..247205.09 rows=100000 width=72) (actual time=722.003..749.696 rows=100000 loops=1)
   Buffers: shared hit=16192 read=35063
   ->  Sort  (cost=246955.09..251944.99 rows=1995958 width=72) (actual time=722.001..737.715 rows=100000 loops=1)
         Sort Key: userid
         Sort Method: top-N heapsort  Memory: 20207kB
         Buffers: shared hit=16192 read=35063
         ->  Seq Scan on users  (cost=0.00..71214.58 rows=1995958 width=72) (actual time=8.584..294.605 rows=1995958 loops=1)
               Buffers: shared hit=16192 read=35063
 Planning time: 0.070 ms
 Execution time: 757.495 ms
(10 rows)

, , Mac/ , .