Postgresql – min()/max() on multi-column timestamp index

performancepostgresqlpostgresql-performance

I'm finding it difficult to understand why there's a bunch of heap fetches going on in this query. To my understanding, when there's no nulls (at either end) in the index, reverse searching the index should be as fast as direct searching and vice versa.

I suspect that the forward/backwards scan is actually a red herring, but I'm unable to recognize any other meaningful difference in this explain output.

Here's the table layout. I've anonymized the first two columns which I believe aren't relevant to the problem, but I kept them and their indexes in for completeness.

testqueuedb=> \d+ queue
                                                                  Table "public.queue"
        Column         |           Type           |                          Modifiers                          | Storage  | Stats target | Description
-----------------------+--------------------------+-------------------------------------------------------------+----------+--------------+-------------
 foo                   | character varying(64)    | not null                                                    | extended |              |
 bar                   | numeric(6,0)             | not null                                                    | main     |              |
 worker                | character varying(32)    | not null                                                    | extended |              |
 queued                | timestamp with time zone | not null default (timeofday())::timestamp without time zone | plain    |              |
Indexes:
    "queue_idx_job" btree (foo, bar, worker)
    "queue_idx_worker" btree (worker, queued)
Foreign-key constraints:
    "queue_fk_worker" FOREIGN KEY (worker) REFERENCES workers(worker)

And here are the different min/max explains.

testqueuedb=> explain (analyze, buffers) select min(queued) from queue where worker = 'workername';
                                                                        QUERY PLAN                              
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.59..0.60 rows=1 width=0) (actual time=1019.490..1019.490 rows=1 loops=1)
   Buffers: shared hit=20194 read=1
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.42..0.59 rows=1 width=8) (actual time=1019.485..1019.486 rows=1 loops=1)
           Buffers: shared hit=20194 read=1
           ->  Index Only Scan using queue_idx_worker on queue  (cost=0.42..55480.93 rows=330371 width=8) (actual time=1019.483..1019.483 rows=1 loops=1)
                 Index Cond: ((worker = 'workername'::text) AND (queued IS NOT NULL))
                 Heap Fetches: 20224
                 Buffers: shared hit=20194 read=1
 Planning time: 0.197 ms
 Execution time: 1019.529 ms
(11 rows)

testqueuedb=> explain (analyze, buffers) select max(queued) from queue where worker = 'workername';
                                                                         QUERY PLAN                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=0.59..0.60 rows=1 width=0) (actual time=0.508..0.509 rows=1 loops=1)
   Buffers: shared hit=2 read=3
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.42..0.59 rows=1 width=8) (actual time=0.503..0.503 rows=1 loops=1)
           Buffers: shared hit=2 read=3
           ->  Index Only Scan Backward using queue_idx_worker on queue  (cost=0.42..55480.93 rows=330371 width=8) (actual time=0.502..0.502 rows=1 loops=1)
                 Index Cond: ((worker = 'workername'::text) AND (queued IS NOT NULL))
                 Heap Fetches: 1
                 Buffers: shared hit=2 read=3
 Planning time: 0.215 ms
 Execution time: 0.546 ms
(11 rows)

I find the heap fetches in this first example particularly confusing. Does it all come down to buffering?

The Postgres version is 9.5.5.

There are about 500,000 rows for each worker in the table and very few distinct workers — less than ten — which makes me think the index isn't really structured correctly to begin with, but I'm interested in the difference in these queries regardless.

Best Answer

I'm pretty sure this is due to the choice of timestamptz as the datatype for the queued column. Postgres has to visit all the columns to make sure it has found the true max, because of time zone considerations. This is why the index scan shows such a high count.

You should change the queued datatype to an int (or bigint) and autoincrement it with a sequence. (You can of course leave the timestamp column in if you need the value).