Create the SQL query "SELECT * FROM myTable LIMIT 10" using dplyr

Question

Suppose I have a connection to an external database con.

I would like to use dplyrto play this request

SELECT var1, var2, var3 from myTable LIMIT 10

I tried

qry <- tbl(con, "myTable") %>%
    select(var1) %>%
    filter(between(row_number(), 1, 10)

but it does not give the desired result, and the request that it produces is much slower than the one that I receive after.

Generated request

SELECT "var1",
FROM SELECT "var1", row_number() OVER () AS "zzz25"
FROM SELECT "var1" AS "var1"
FROM myTable "yhydrqlhho") "zsdfunxidf"
WHERE zzz25 BETWEEN 1.0 AND 10.0)

When I send this request to the database, it works for a very long time. When i send

SELECT var1 from myTable limit 10 

The result returns instantly.

Answer 1

You can try head(10), it generates the correct SQL query for Postgres:

tbl(con, 'my_table') %>% select(var1, var2) %>% head(6) %>% explain()
# here con is a PostgreSQL connection

#<SQL>
#SELECT "var1" AS "var1", "var2" AS "var2"
#FROM "my_table"
#LIMIT 6