Message-ID: <3cfa05ce-d7f2-b2ba-313f-ed0c3c067fc4@enterprisedb.com>
Date: Thu, 21 Dec 2023 16:32:51 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.15.1
Subject: Re: index prefetching
Content-Language: en-US
To: Andres Freund <andres@anarazel.de>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Georgios <gkokolatos@protonmail.com>
References: <20230609000600.syqy447e6metnvyj@awork3.anarazel.de>
 <a062ac09-c574-1d6a-22b6-f3cc0d28ed42@enterprisedb.com>
 <20230610203456.5gancfekm4pj4pbs@awork3.anarazel.de>
 <6030d836-e8b7-e7b9-2cbb-144309679d03@enterprisedb.com>
 <8c86c3a6-074e-6c88-3e7e-9452b6a37b9b@enterprisedb.com>
 <3cd40425-965a-5ce1-1af3-d51971c44b93@enterprisedb.com>
 <8ec36f51-b863-60e3-20e2-b9c981c5ce5e@enterprisedb.com>
 <06bb7d02-2c44-3062-731e-a735ba13da7e@enterprisedb.com>
 <367160ea-b1ed-4481-e804-bca509128878@enterprisedb.com>
 <cd63c8bc-3d3c-00ab-f7eb-fa55969c3234@enterprisedb.com>
 <20231221132742.kqxt3iujna3z33ab@alap3.anarazel.de>
From: Tomas Vondra <tomas.vondra@enterprisedb.com>
In-Reply-To: <20231221132742.kqxt3iujna3z33ab@alap3.anarazel.de>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/3cfa05ce-d7f2-b2ba-313f-ed0c3c067fc4%40enterprisedb.com>
Precedence: bulk


On 12/21/23 14:27, Andres Freund wrote:
> Hi,
> 
> On 2023-12-09 19:08:20 +0100, Tomas Vondra wrote:
>> But there's a layering problem that I don't know how to solve - I don't
>> see how we could make indexam.c entirely oblivious to the prefetching,
>> and move it entirely to the executor. Because how else would you know
>> what to prefetch?
> 
>> With index_getnext_tid() I can imagine fetching XIDs ahead, stashing
>> them into a queue, and prefetching based on that. That's kinda what the
>> patch does, except that it does it from inside index_getnext_tid(). But
>> that does not work for index_getnext_slot(), because that already reads
>> the heap tuples.
> 
>> We could say prefetching only works for index_getnext_tid(), but that
>> seems a bit weird because that's what regular index scans do. (There's a
>> patch to evaluate filters on index, which switches index scans to
>> index_getnext_tid(), so that'd make prefetching work too, but I'd ignore
>> that here.
> 
> I think we should just switch plain index scans to index_getnext_tid(). It's
> one of the primary places triggering index scans, so a few additional lines
> don't seem problematic.
> 
> I continue to think that we should not have split plain and index only scans
> into separate files...
> 

I do agree with that opinion. Not just because of this prefetching
thread, but also because of the discussions about index-only filters in
a nearby thread.

> 
>> There are other index_getnext_slot() callers, and I don't
>> think we should accept does not work for those places seems wrong (e.g.
>> execIndexing/execReplication would benefit from prefetching, I think).
> 
> I don't think it'd be a problem to have to opt into supporting
> prefetching. There's plenty places where it doesn't really seem likely to be
> useful, e.g. doing prefetching during syscache lookups is very likely just a
> waste of time.
> 
> I don't think e.g. execReplication is likely to benefit from prefetching -
> you're just fetching a single row after all. You'd need a lot of dead rows to
> make it beneficial.  I think it's similar in execIndexing.c.
> 

Yeah, systable scans are unlikely to benefit from prefetching of this
type. I'm not sure about execIndexing/execReplication, it wasn't clear
to me but maybe you're right.

> 
> I suspect we should work on providing executor nodes with some estimates about
> the number of rows that are likely to be consumed. If an index scan is under a
> LIMIT 1, we shoulnd't prefetch. Similar for sequential scan with the
> infrastructure in
> https://postgr.es/m/CA%2BhUKGJkOiOCa%2Bmag4BF%2BzHo7qo%3Do9CFheB8%3Dg6uT5TUm2gkvA%40mail.gmail.com
> 

Isn't this mostly addressed by the incremental ramp-up at the beginning?
Even with target set to 1000, we only start prefetching 1, 2, 3, ...
blocks ahead, it's not like we'll prefetch 1000 blocks right away.

I did initially plan to also consider the number of rows we're expected
to need, but I think it's actually harder than it might seem. With LIMIT
for example we often don't know how selective the qual is, it's not like
we can just stop prefetching after the reading the first N tids. With
other nodes it's good to remember those are just estimates - it'd be
silly to be bitten both by a wrong estimate and also prefetching doing
the wrong thing based on an estimate.


regards

-- 
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company