1

As of PostgreSQL 10.x, foreign tables support aggregate pushdown.

I noticed that sum and count operations are being pushed to the remote server but min and max are not pushed the same way.

 select min(col) from foreign_table

shows a foreign scan with an ORDER BY, followed by a LIMIT applied locally.

In my case, the remote server is actually Redshift rather than another PostgreSQL instance, so this is going to be slower than issuing the min right on the query the same way a sum or count would have been, and oddly, if you do a query like

 select count(col), min(col) from foreign_table

the min will be pushed on the query.

Why is postgres_fdw treating min/max differently and is there an optimizer setting that will get min/max to be treated same as other aggregate functions?

wrschneider
  • 17,913
  • 16
  • 96
  • 176

1 Answers1

0

This is actually the best and fastest way to do it. Did you test your assertion that “this is going to be slower than issuing the min right on the query”?

PostgreSQL fetches tuples from executor nodes on demand, so what will happen is that the first row is fetched from the foreign scan (via the index), then the LIMIT will be done and no more rows will be fetched. This is essentially the same thing that would happen if the min were executed remotely.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • This is only true if the remote column is indexed. If you have to do a full scan, sorting is O(N log N) while searching for min is O(N) -- the remote server doesn't know you only care about the first row returned. In my case the remote server is Redshift, so the characteristics of sort vs. min are also different. – wrschneider Aug 27 '18 at 13:54
  • You show no evidence for the assertion in the last sentence. Are you using remote estimates? If yes, the existence of non-existence of a remote index will influence the plan chosen. – Laurenz Albe Aug 31 '18 at 12:28