Re: Draft LIMIT pushdown to Append and MergeAppend patch

On 8 Oct 2023, at 03:33, Andy Fan <zhihui.fan1213@gmail.com> wrote:

On Sun, Oct 8, 2023 at 5:04 AM Michał Kłeczek <michal@kleczek.org> wrote:

Hi All,

Attached is a second version of the patch.

The goal is to:
1. Apply LIMIT as early as possible - especially to apply LIMIT in partition scans

For the patches for performance improvement,  it is better to provide

an example to show how much benefits we can get.  As for this case,

I'm doubtful it can work as an improvement. 

2. Enable LIMIT pushdown for FDW partitions.

The same as above,  some testing is helpful. 

FDW does not push down LIMIT & ORDER BY with sharding (partitions) postgresql.org

While obviously permofmance testing is needed to confirm any real improvements

I now (after your feedback) have second thoughts if it is worth pursuing at all.

Could you elaborate a little why you think it won’t work as an improvement?

Is it because in practice LIMIT _is_ pushed down already during execution?

From what I understand postgres_fdw does indeed fetch on demand.

OTOH pushing down LIMIT is considered an improvement (as witnessed in the postgres_fdw code itself after d50d172e51)

Care to provide some more information?

Thanks,

--

Michal