Re: Custom explain options

On 1/12/24 20:30, Konstantin Knizhnik wrote:

On 12/01/2024 7:03 pm, Tomas Vondra wrote:

On 10/21/23 14:16, Konstantin Knizhnik wrote:

Hi hackers,

EXPLAIN statement has a list of options (i.e. ANALYZE, BUFFERS,
COST,...) which help to provide useful details of query execution.
In Neon we have added PREFETCH option which shows information about page
prefetching during query execution (prefetching is more critical for
Neon
architecture because of separation of compute and storage, so it is
implemented not only for bitmap heap scan as in Vanilla Postgres, but
also for seqscan, indexscan and indexonly scan). Another possible
candidate  for explain options is local file cache (extra caching layer
above shared buffers which is used to somehow replace file system cache
in standalone Postgres).

Not quite related to this patch about EXPLAIN options, but can you share
some details how you implemented prefetching for the other nodes?

I'm asking because I've been working on prefetching for index scans, so
I'm wondering if there's a better way to do this, or how to do it in a
way that would allow neon to maybe leverage that too.

regards

Yes, I am looking at your PR. What we have implemented in Neon is more
specific to Neon architecture where storage is separated from compute.
So each page not found in shared buffers has to be downloaded from page
server. It adds quite noticeable latency, because of network roundtrip.
While vanilla Postgres can rely on OS file system cache when page is not
found in shared buffer (access to OS file cache is certainly slower than
to shared buffers
because of syscall and copying of page, but performance penaly is not
very large - less than 15%), Neon has no local files and so has to send
request to the socket.

This is why we have to perform aggressive prefetching whenever it is
possible (when it it is possible to predict order of subsequent pages).
Unlike vanilla Postgres which implements prefetch only for bitmap heap
scan, we have implemented it for seqscan, index scan, indexonly scan,
bitmap heap scan, vacuum, pg_prewarm.
The main difference between Neon prefetch and vanilla Postgres prefetch
is that first one is backend specific. So each backend prefetches only
pages which it needs.
This is why we have to rewrite prefetch for bitmap heap scan, which is
using `fadvise` and assumes that pages prefetched by one backend in file
cache, can be used by any other backend.

I do understand why prefetching is important in neon (likely more than
for core postgres). I'm interested in how it's actually implemented,
whether it's somehow similar to how my patch does things or in some
different (perhaps neon-specific way), and if the approaches are
different then what are the pros/cons. And so on.

So is it implemented in the neon-specific storage, somehow, or where/how
does neon issue the prefetch requests?

Neon mostly preservers Postgres prefetch mechanism, so we are using PrefetchBuffer which checks if page is present in shared buffers
and if not - calls smgrprefetch. We are using own storage manager implementation which instead of reading pages from local disk, download them from page server.
And prefetch implementation in Neon storager manager is obviously also different from one in vanilla Postgres which uses posix_fadvise.
Neon prefetch implementation inserts prefetch request in ring buffer and sends it to the server. When read operation is performed we check if there is correspondent prefetch request in ring buffer and if so - waits its completion.

As I already wrote - prefetch is done locally for each backend. And each backend has its own connection with page server. It  can be changed in future when we implement multiplexing of page server connections. But right now prefetch is local. And certainly prefetch can improve performance only if we correctly predict subsequent page requests.
If not - then page server does useless jobs and backend has to waity and consume all issues prefetch requests. This is why in prefetch implementation for most of nodes we  start with minimal prefetch distance and then increase it. It allows to perform prefetch only for such queries where it is really efficient (OLAP) and doesn't degrade performance of simple OLTP queries.

Out prefetch implementation is also compatible with parallel plans, but here we need to preserve some range of pages for each parallel workers instead of picking page from some shared queue on demand. It is one of the major difference with Postgres prefetch using posix_fadvise: each backend shoudl prefetch only those pages which it will going to read.

Concerning index scan we have implemented two different approaches: for
index only scan we  try to prefetch leave pages and for index scan we
prefetch referenced heap pages.

In my experience the IOS handling (only prefetching leaf pages) is very
limiting, and may easily lead to index-only scans being way slower than
regular index scans. Which is super surprising for users. It's why I
ended up improving the prefetcher to optionally look at the VM etc.

Well, my assumption was the following: prefetch is most efficient for OLAP queries.
Although HTAP (hybrid transactional/analytical processing) is popular trend now,
classical model is that analytic queries are performed on "historical" data, which was already proceeded by vacuum and all-visible bits were set in VM.
May be this assumption is wrong but it seems to me that if most heap pages are not marked as all-visible, then  optimizer should prefetch bitmap scan to index-only scan.
And for combination of index and heap bitmap scans we can efficiently prefetch both index and heap pages.

In both cases we start from prefetch distance 0 and increase it until it
reaches `effective_io_concurrency` for this relation. Doing so we try to
avoid prefetching of useless pages and slowdown of "point" lookups
returning one or few records.

Right, the regular prefetch ramp-up. My patch does the same thing.

If you are interested, you can look at our implementation in neon repo:
all source are available. But briefly speaking, each backend has its own
prefetch ring (prefetch requests which are waiting for response). The
key idea is that we can send several prefetch requests to page server
and then receive multiple replies. It allows to increased speed of OLAP
queries up to 10 times.

Can you point me to the actual code / branch where it happens? I did
check the github repo, but I don't see anything relevant in the default
branch (REL_15_STABLE_neon). There are some "prefetch" branches, but
those seem abandoned.

Implementation of prefetch mecnahism is in Neon extension:
https://github.com/neondatabase/neon/blob/60ced06586a6811470c16c6386daba79ffaeda13/pgxn/neon/pagestore_smgr.c#L205

But concrete implementation of prefetch for particular nodes is certainly inside Postgres.
For example, if you are interested how it is implemented for index scan, then please look at:
https://github.com/neondatabase/postgres/blob/c1c2272f436ed9231f6172f49de219fe71a9280d/src/backend/access/nbtree/nbtsearch.c#L844
https://github.com/neondatabase/postgres/blob/c1c2272f436ed9231f6172f49de219fe71a9280d/src/backend/access/nbtree/nbtsearch.c#L1166
https://github.com/neondatabase/postgres/blob/c1c2272f436ed9231f6172f49de219fe71a9280d/src/backend/access/nbtree/nbtsearch.c#L1467
https://github.com/neondatabase/postgres/blob/c1c2272f436ed9231f6172f49de219fe71a9280d/src/backend/access/nbtree/nbtsearch.c#L1625
https://github.com/neondatabase/postgres/blob/c1c2272f436ed9231f6172f49de219fe71a9280d/src/backend/access/nbtree/nbtsearch.c#L2629

Heikki thinks that prefetch can be somehow combined with async-io
proposal (based on io_uring). But right now they have nothing in common.

I can imagine async I/O being useful here, but I find the flow of I/O
requests is quite complex / goes through multiple layers. Or maybe I
just don't understand how it should work.