I'm using sqlx to perform a query in my Go code. The query has a long list of values (~10,000) that I need to filter on in the WHERE IN (?) clause. This causes a huge slowdown in performance. How can I optimize such a query? Also, this query is being performed on a Redshift cluster, so indexing a column isn't a solution to improve the query performance.
values := []int64{143, 123, 123, 542....} // ~10,000 elements
query, args, err := sqlx.In(query, values)
if err != nil {
return nil, err
}
query = dbInterface.Rebind(query)
err = dbInterface.Select(&list, query, args...)
Query:
SELECT * FROM table_name WHERE some_id IN (?) ORDER BY created_at;
I ultimately created a TEMP TABLE for the ids and performed a JOIN. This greatly improved the query performance.
You need to make sqlx produce one of these:
the values syntax:
where some_id in (values (143), (123))
join an array:
select *
from
t
inner join
unnest(array[143, 123]) u (id) on u.id = t.id