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
