7

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.

  • 1
    Does select queued from queue where worker = 'workername' order by queued limit 1; show the same behaviour/explain? – ypercubeᵀᴹ Jan 23 '17 at 14:04
  • @ypercubeᵀᴹ Yes, it does. Similarly, flipping the order to desc in that query makes it nearly instantaneous. – user115675 Jan 23 '17 at 14:37
  • 1
    @user115675 I have just tried to reproduce your problem on postgres 9.5 - no success. My queue table had same distribution as you described. About 400MB table, about 150 MB index. Plans are the same, but buffer couts are much smaller and there is no heap fetch anomaly. Please provide more info on dataset (exact size, avg lenth etc) – filiprem Jun 29 '17 at 22:13
  • Does this apply? dba.stackexchange.com/help/merging-accounts – Michael Green Sep 7 '17 at 10:40
  • 2
    It's a good practice to run each explain twice to see effects of caching. Does this make a difference? – Franc Drobnič Sep 21 '17 at 11:13
1

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).

  • I don't think so timestamptz is just a timestamp in zone UTC, no conversions are needed to compare them. (on disk it's a bigint count of microseconds) – Jasen Jul 31 '18 at 9:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.