Remote SQL in the above plan shows us that count(*) gets computed on the foreign server. Remote query: SELECT count(*), `id` FROM `suraj`.`orders` GROUP BY 2 But with aggregate push-down support in mysql_fdw, now the query plan looks like: EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM orders GROUP BY id We can see that it will fetch all the rows from the orders table from the remote server and then evaluate count(*) locally using HashAggregate. Remote query is the SQL query sent to the remote server to fetch all the rows from the remote table. Remote query: SELECT `id` FROM `suraj`.`orders` Before this enhancement, if you had an aggregate query, it would produce a plan like: EXPLAIN (VERBOSE, COSTS OFF) SELECT count(*) FROM ORDERS GROUP BY id This gives a very good performance boost for the cases where aggregates can be pushed down.įor better understanding, we will see how the explain plan looks without and with aggregate push-down. So, for the above example, count(*) will be executing on the remote side. Now, with the aggregate push-down feature, aggregate functions are getting pushed down to the remote MySQL server instead of fetching all of the rows and aggregating them locally. This happened because simple count(*) on a foreign table mysql_fdw had to fetch all the rows from the remote MySQL server just to count them. Previously we experienced some customer reports where they complained of slow-performing queries which are using aggregate functions. Continuing the spree, the mysql_fdw release 2.7.0 now announces another exciting feature - aggregate push-down. In my previous blog, we talked about an exciting feature - join push-down for mysql_fdw. We have exciting news for mysql_fdw users.
0 Comments
Leave a Reply. |
Details
AuthorDan ArchivesCategories |