4

Yet another question from my discovery of the great new EXPLAIN options in PostgreSQL. This one focuses on the BUFFERS option.

Here is EXPLAIN:

EXPLAIN (ANALYZE, BUFFERS) SELECT event_time FROM ui_events_v2  WHERE page ~ 'foo' LIMIT 1;
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1539.68 rows=1 width=8) (actual time=0.858..0.858 rows=1 loops=1)
   Buffers: shared read=10
   I/O Timings: read=0.735
   ->  Seq Scan on ui_events_v2  (cost=0.00..3313394.58 rows=2152 width=8) (actual time=0.857..0.857 rows=1 loops=1)
         Filter: (page ~ 'foo'::text)
         Rows Removed by Filter: 112
         Buffers: shared read=10
         I/O Timings: read=0.735
 Planning Time: 6.455 ms
 Execution Time: 0.877 ms

It is quite fast - and this query is very slow on a cold start. This is a 30M row table, and seven rows have page containing substring foo. The first row matching this query would seem to show up 604k pages in, which would be ~20% of the ~3M pages mentioned in pg_class:

SELECT min(ctid) FROM ui_events_v2 WHERE page ~ 'foo';
     min
-------------
 (604435,10)

My assumption was that even if every page of this table were in the PostgreSQL or operating system cache, it still needs to walk some linear list of pages for a sequential scan. The section Buffers: shared read=10, and the 0.877 ms execution time suggests to me that it's somehow "picking up where it left off" and starting at pages that are far from the beginning. I thought it moved through page IDs and peeked into a cache trying for each page as it moved, but does it perhaps start instead from the cache itself? If that were the case, how does it discover the non-cached pages?

I know that rows aren't guaranteed to be found in any particular order, but I figured that within the same query strategy, the path followed would be relatively similar.

9

If multiple processes are trying to seq scan the same table, it will try to line them all up so that all read the same pages at about the same time, to maximize cache hits. It does this by having the late comers start in the middle (where ever the existing ones happen to be at), and wrap around the end to finish the table at the page before where it started. A side effect of this is that if one seq scan stops early, the next one will start up on the same page where the last one stopped. So if the entire LIMIT can be satisfied by rows from one page, a scan like yours will just scan the same page over and over.

You can turn this optimization off with set synchronize_seqscans=off, then they all start from the first block in the table.

  • Very neat! I'm also a little confused how it was found in cache. I was reading yesterday (interdb.jp/pg/pgsql08.html) that seq scans don't really write to the PG cache if it's a big table (more than 1/4th of shared_buffers size), because it could quickly "ruin" the cache, and that there's a soon-discarded 256kB ring buffer used. Since this table is bigger than memory, I'm wondering how this page got cached, which as far as I know is happening from seq scanning. Does it make the determination not on the entire relation size but on an estimate of rows to read? – dcc310 Nov 27 '20 at 23:36
  • 1
    @dcc310 That article is slightly wrong. The ring of buffers is a subset of the shared buffers, it is not separate from it. Otherwise there would be no mechanism to prevent one process from writing out its version of the page while another reads a torn copy into a different spot. All the ring does is provide the process using it an alternative way to choose what buffer slot to read into next (and thus who to evict next). – jjanes Nov 28 '20 at 0:22

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.