R dplyr query does not use table indexes in PostgreSQL?

user_10207958 0
Viewed 84

I want to use an R notebook to connect to a remote PostgreSQL database using the dplyr package. The problem is that the R function is slow and when I viewed the execution plan I saw that it does not use the table's index, but if I run the same query in PostgreSQL it is much faster because the index is used.

In R:

q <- tbl(con, "mytable")%>%
    filter(a==1000, b>500)%>%
    select(id) %>%
    explain()

#<SQL>
#SELECT "id"
#FROM "mytable"
#WHERE (("a" = 1000.0) AND ("b" > 500.0))
#
#<PLAN>
#Seq Scan on mytable
#  Filter: (((b)::numeric > 500.0) AND ((a)::numeric = 1000.0))

In the same R notebook but in an SQL block:

{sql connection=con}
EXPLAIN ANALYZE SELECT id FROM mytable WHERE a=1000 AND b>500;

#Bitmap Heap Scan on mytable 
#Recheck Cond: (a = 1000)
#Filter: (b > 500)
#Rows Removed by Filter: 44963
#Heap Blocks: exact=390
#-> Bitmap Index Scan on idx_mytable_a
#Index Cond: (a = 1000)

Why are the plans different when they use the same connection?

UPDATE

The R function interprets 1000 as 1000.0 that is the cause of the difference:

{sql connection=con}
EXPLAIN ANALYZE SELECT id FROM mytable WHERE a=1000.0 AND b>500.0;

#Seq Scan on mytable
#  Filter: (((b)::numeric > 500.0) AND ((a)::numeric = 1000.0))

How to avoid this conversion?

dplyr r postgresql
1 Answers

Answering my own question, but curious too see a more elegant solution.

If I explicitly say to interpret the number as integer it uses the index:

q <- tbl(con, "mytable")%>%
    filter(a==as.integer(1000), b>500)%>%
    select(id) %>%
    explain()

#<SQL>
#SELECT "id"
#FROM "mytable"
#WHERE (("a" = CAST(1000.0 AS INTEGER)) AND ("b" > 500.0))
#
#<PLAN>
#Bitmap Heap Scan on mytable
#  Recheck Cond: (a = 1000)
#  Filter: ((b)::numeric > 500.0)
#  ->  Bitmap Index Scan on idx_mytable_a
#        Index Cond: (a = 1000)
user_102079580
Related