Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPNrG-00BC0B-Oe for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jan 2024 14:22:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rPNrE-00Fh8D-6C for pgsql-hackers@arkaria.postgresql.org; Mon, 15 Jan 2024 14:22:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rPNrD-00Fh85-MB for pgsql-hackers@lists.postgresql.org; Mon, 15 Jan 2024 14:22:31 +0000 Received: from mail-lf1-x12a.google.com ([2a00:1450:4864:20::12a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rPNr5-001wU7-Hl for pgsql-hackers@postgresql.org; Mon, 15 Jan 2024 14:22:30 +0000 Received: by mail-lf1-x12a.google.com with SMTP id 2adb3069b0e04-50e7b273352so9550306e87.1 for ; Mon, 15 Jan 2024 06:22:22 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=garret-ru.20230601.gappssmtp.com; s=20230601; t=1705328541; x=1705933341; darn=postgresql.org; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=go3OdTbhkdsWdouSc6NSJxWEIJm54f3ypPPsBGat2+I=; b=x04s4oHrlzRb6qjMOd+0wehaCoUWRvJytjKCh2nseBgTl0HCTFpCVVk5DQG5P+eztE 7dEl9hBVQDkd+RxmZwW+4UnlGSMFlHCRTfLQiLBGIsuQDQMA4pkYK7JHMjnPVmel+Vuy lG63T560CN781mpBIWFIoPYwh6+ah/2ljRL+3zLR6K3iniJJmG0JFhcB4nHb2iaSHiK8 ThWByUIEhD6JgFZQwGgw+xTjDxTOFgJXACwsqSEcwlcSLuXigL+hQcDSSHZbSSZ1sNeA 5GNBK575jNcfQQfdh3hHAOtx0wI/C0VvaI+QopdfiQZPFnwf0tu7pRo3yRiCkHMab6Xj Zm4w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1705328541; x=1705933341; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=go3OdTbhkdsWdouSc6NSJxWEIJm54f3ypPPsBGat2+I=; b=P59cCRDFR4bPElr9lj3Y+B+Jsx6QBJYEFm2ckReEOQBkbDtpptGucYWvySOYByf7sk sHsCQ5DSOMSbVlFu6CJaAOPqFRiwQAZM1ijnv0AQPB5/+sNHb5yiDrYzzQbNpkFmLFQ2 tn4Lk3JAiYDVSf6ehKVPjnks75SLS89nkZmGq8U4hn1doYycYCvizO6cb6Lla5nTMcvO NJFGPTv7PD6KIIf1KyZTlmL68acCBfPcws65DLrwK5/jg4DFBcZ7sPoA/+fvsFpfJd2w /XZM9rG81s26jazppet1Irqii0xBhHhv8e7CHheY8/9/JHQVxnHevLCQRPFKE/PUkSsl czNw== X-Gm-Message-State: AOJu0Yy0IqnjC6w/ncAs677WvKHtNJhI9oJFt4pxUWfXiOIz/kkA2cJD fn9o0shwNDcmyE96L+EgQV6Kgn0WXlfw6WXiPQyUhaF8Mw== X-Google-Smtp-Source: AGHT+IHLeOTV72pY6WmHOgEwtgsgshjkc+Th7YEzEl9jQu3sG3i+6UBmSSpO5qgvgOvF/xkJXrFerA== X-Received: by 2002:a05:6512:1582:b0:50e:e2e0:9fe6 with SMTP id bp2-20020a056512158200b0050ee2e09fe6mr2965995lfb.16.1705328540454; Mon, 15 Jan 2024 06:22:20 -0800 (PST) Received: from [192.168.2.11] ([217.175.216.56]) by smtp.gmail.com with ESMTPSA id ap3-20020a17090735c300b00a2c879e89a8sm4895511ejc.188.2024.01.15.06.22.19 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 15 Jan 2024 06:22:19 -0800 (PST) Content-Type: multipart/alternative; boundary="------------RSyp3fbGdcG01i2fKKts31Mi" Message-ID: <57bc73d0-5f70-40bb-8d9e-ba4bc1f59d47@garret.ru> Date: Mon, 15 Jan 2024 16:22:18 +0200 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Custom explain options Content-Language: en-US To: Tomas Vondra , PostgreSQL Hackers References: <6cd5caa7-06e1-4460-bf35-00a59da3f677@garret.ru> <629afb5e-9327-422c-a29f-4484279939b4@enterprisedb.com> <04aeec17-ad57-4d90-b2f1-7881369b7920@garret.ru> <50bf548d-44a1-49d8-aac9-6e4988badaae@enterprisedb.com> From: Konstantin Knizhnik In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------RSyp3fbGdcG01i2fKKts31Mi Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 14/01/2024 11:47 pm, Tomas Vondra wrote: > The thing that was not clear to me is who decides what to prefetch, > which code issues the prefetch requests etc. In the github links you > shared I see it happens in the index AM code (in nbtsearch.c). It is up to the particular plan node (seqscan, indexscan,...) which pages to prefetch. > > That's interesting, because that's what my first prefetching patch did > too - not the same way, ofc, but in the same layer. Simply because it > seemed like the simplest way to do that. But the feedback was that's the > wrong layer, and that it should happen in the executor. And I agree with > that - the reasons are somewhere in the other thread. > I read the arguments in https://www.postgresql.org/message-id/flat/8c86c3a6-074e-6c88-3e7e-9452b6a37b9b%40enterprisedb.com#fc792f8d013215ace7971535a5744c83 Separating prefetch info in index scan descriptor is really good idea. It will be amazing to have generic prefetch mechanism for all indexes. But unfortunately I do not understand how it is possible. The logic of index traversal is implemented inside AM. Executor doesn't know it. For example for B-Tree scan we can prefetch: - intermediate pages - leave pages - referenced by TID heap pages Before we load next intermediate page, we do not know next leave pages. And before we load next leave page, we can not find out TIDs from this page. Another challenge - is how far we should prefetch (as far as I understand both your and our approach using dynamically extended prefetch window) > Based on what I saw in the neon code, I think it should be possible for > neon to use "my" approach too, but that only works for the index scans, > ofc. Not sure what to do about the other places. We definitely need prefetch for heap scan (it gives the most advantages in performance), for vacuum  and also for pg_prewarm. Also I tried to implement it for custom indexes such as pg_vector. I still not sure whether it is possible to create some generic solution which will work for all indexes. I have also tried to implement alternative approach for prefetch based on access statistic. It comes from use case of seqscan of table with larger toasted records. So for each record we have to extract its TOAST data. It is done using standard index scan, but unfortunately index prefetch doesn't help much here: there is usually just one TOAST segment and so prefetch just have no chance to do something useful. But as far as heap records are accessed sequentially, there is good chance that toast table will also be accessed mostly sequentially. So we just can count number of sequential requests to each relation and if ratio or seq/rand  accesses is above some threshold we can prefetch next pages of this relation. This is really universal approach but ... working mostly for TOAST table. >> 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. >> > Not sure I understand what's so important about prefetches being "local" > for each backend. I mean even in postgres each backend prefetches it's > own buffers, no matter what the other backends do. Although, neon > probably doesn't have the cross-backend sharing through shared buffers > etc. right? Sorry if my explanation was not clear:( > I mean even in postgres each backend prefetches it's own buffers, no matter what the other backends do. This is exactly the difference. In Neon such approach doesn't work. Each backend maintains it's own prefetch ring. And if prefetched page was not actually received, then the whole pipe is lost. I.e. backend prefetched pages 1,5,10. Then it need to read page 2. So it has to consume responses for 1,5,10 and issue another request for page 2. Instead of improving speed we are just doing extra job. So each backend should prefetch only those pages which it is actually going to read. This is why prefetch approach used in Postgres for example for parallel bitmap heap scan doesn't work for Neon. If you do `posic_fadvise` then prefetched page is placed in OS cache and can be used by any parallel worker. But in Neon each parallel worker should be given its own range of pages to scan and prefetch only this pages. > >> Well, my assumption was the following: prefetch is most efficient forOLAP 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. > I think this assumption is generally reasonable, but it hinges on the > assumption that OLAP queries have most indexes recently vacuumed and > all-visible. I'm not sure it's wise to rely on that. > > Without prefetching it's not that important - the worst thing that would > happen is that the IOS degrades into regular index-scan. > I think that it is also problem without prefetch. There are cases where seqscan or bitmap heap scan are really much faster then IOS because last one has to perform a lot of visibility checks. Yes, certainly optimizer takes in account percent of all-visible pages.But with it is not tricial to adjust optimizer parameters so that it can really choose fastest plan. > But withprefetching these plans can "invert" with respect to cost. > > I'm not saying it's terrible or that IOS must have prefetching, but I > think it's something users may run into fairly often. And it led me to > rework the prefetching so that IOS can prefetch too ... > > I think that inspecting VM for prefetch is really good idea. > Thanks! Very helpful. As I said, I ended up moving the prefetching to > the executor. For indexscans I think it should be possible for neon to > benefit from that (in a way, it doesn't need to do anything except for > overriding what PrefetchBuffer does). Not sure about the other places > where neon needs to prefetch, I don't have ambition to rework those. > Once your PR will be merged, I will rewrite Neon prefetch implementation fopr indexces using your approach. --------------RSyp3fbGdcG01i2fKKts31Mi Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit


On 14/01/2024 11:47 pm, Tomas Vondra wrote:
The thing that was not clear to me is who decides what to prefetch,
which code issues the prefetch requests etc. In the github links you
shared I see it happens in the index AM code (in nbtsearch.c).


It is up to the particular plan node (seqscan, indexscan,...) which pages to prefetch.



That's interesting, because that's what my first prefetching patch did
too - not the same way, ofc, but in the same layer. Simply because it
seemed like the simplest way to do that. But the feedback was that's the
wrong layer, and that it should happen in the executor. And I agree with
that - the reasons are somewhere in the other thread.

I read the arguments in 

https://www.postgresql.org/message-id/flat/8c86c3a6-074e-6c88-3e7e-9452b6a37b9b%40enterprisedb.com#fc792f8d013215ace7971535a5744c83

Separating prefetch info in index scan descriptor is really good idea. It will be amazing to have generic prefetch mechanism for all indexes.
But unfortunately I do not understand how it is possible. The logic of index traversal is implemented inside AM. Executor doesn't know it.
For example for B-Tree scan we can prefetch:

- intermediate pages
- leave pages
- referenced by TID heap pages

Before we load next intermediate page, we do not know next leave pages.
And before we load next leave page, we can not find out TIDs from this page.

Another challenge - is how far we should prefetch (as far as I understand both your and our approach using dynamically extended prefetch window)

Based on what I saw in the neon code, I think it should be possible for
neon to use "my" approach too, but that only works for the index scans,
ofc. Not sure what to do about the other places.
We definitely need prefetch for heap scan (it gives the most advantages in performance), for vacuum  and also for pg_prewarm. Also I tried to implement it for custom indexes such as pg_vector.  I still not sure whether it is possible to create some generic solution which will work for all indexes.

I have also tried to implement alternative approach for prefetch based on access statistic.
It comes from use case of seqscan of table with larger toasted records. So for each record we have to extract its TOAST data.
It is done using standard index scan, but unfortunately index prefetch doesn't help much here: there is usually just one TOAST segment and so prefetch just have no chance to do something useful. But as far as heap records are accessed sequentially, there is good chance that toast table will also be accessed mostly sequentially. So we just can count number of sequential requests to each relation and if ratio or seq/rand  accesses is above some threshold we can prefetch next pages of this relation. This is really universal approach but ... working mostly for TOAST table. 



      

        
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.



      

      
Not sure I understand what's so important about prefetches being "local"
for each backend. I mean even in postgres each backend prefetches it's
own buffers, no matter what the other backends do. Although, neon
probably doesn't have the cross-backend sharing through shared buffers
etc. right?

    

    


    

    
Sorry if my explanation was not clear:(

    

    
> I mean even in postgres each backend prefetches it's own buffers, no matter what the other backends do.

This is exactly the difference. In Neon such approach doesn't work.
Each backend maintains it's own prefetch ring. And if prefetched page was not actually received, then the whole pipe is lost.
I.e. backend prefetched pages 1,5,10. Then it need to read page 2. So it has to consume responses for 1,5,10 and issue another request for page 2.
Instead of improving speed we are just doing extra job.
So each backend should prefetch only those pages which it is actually going to read.
This is why prefetch approach used in Postgres for example for parallel bitmap heap scan doesn't work for Neon.
If you do `posic_fadvise` then prefetched page is placed in OS cache and can be used by any parallel worker.
But in Neon each parallel worker should be given its own range of pages to scan and prefetch only this pages.


    


      

        
Well, my assumption was the following: prefetch is most efficient forOLAP 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.


      

      
I think this assumption is generally reasonable, but it hinges on the
assumption that OLAP queries have most indexes recently vacuumed and
all-visible. I'm not sure it's wise to rely on that.

Without prefetching it's not that important - the worst thing that would
happen is that the IOS degrades into regular index-scan. 



    

    I think that it is also problem without prefetch. There are cases
    where seqscan or bitmap heap scan are really much faster then IOS
    because last one has to perform a lot of visibility checks. Yes,
    certainly optimizer takes in account percent of all-visible pages. But with it is not tricial to adjust optimizer parameters so that it can really choose fastest plan.



    

      
 But withprefetching these plans can "invert" with respect to cost.

I'm not saying it's terrible or that IOS must have prefetching, but I
think it's something users may run into fairly often. And it led me to
rework the prefetching so that IOS can prefetch too ...




    

    


    

    
I think that inspecting VM for prefetch is really good idea.

    

    


      
Thanks! Very helpful. As I said, I ended up moving the prefetching to
the executor. For indexscans I think it should be possible for neon to
benefit from that (in a way, it doesn't need to do anything except for
overriding what PrefetchBuffer does). Not sure about the other places
where neon needs to prefetch, I don't have ambition to rework those.



    

    
Once your PR will be merged, I will rewrite Neon prefetch
      implementation fopr indexces using your approach.

    


    

  


--------------RSyp3fbGdcG01i2fKKts31Mi--