Re: Qual push down to table AM

On 2025-08-27 22:27:37 +0200, Julien Tachoires wrote:
> Please find attached a patch set proposal intended to implement WHERE
> clauses (qual) push down to the underlying table AM during
> table/sequential scan execution.
> 
> The primary goal of this project is to convert quals to ScanKeys and
> pass them to the table AMs. Table AMs are then allowed to apply early
> tuple filtering during table (sequential) scans. Applying filtering at
> the table storage level is something necessary for non row-oriented
> table storage like columnar storage. Index organized table is another
> table storage that would need quals push down.
> 
> AFAIK, CustomScan is the one and only way to go for having table scan
> using quals pushed down, but each table AM must implement its own
> mechanism. IMHO, having this feature available in core would help the
> development of new table AMs. About Heap, some performance testing
> (detailed at the end of this message) shows between 45% and 60%
> improvement in seq scan execution time when only one tuple is returned
> from the table.

One problem with doing that in the case of heapam is that you're evaluating
scan keys with the buffer lock held - with basically arbitrary expressions
being evaluated. That's an easy path to undetected deadlocks.  You'd have to
redesign the relevant mechanism to filter outside of the lock...

Greetings,

Andres Freund