Using value list in ANY() expression

[danielcondemarin]

I wanted to try this optimisation: https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/
in my own workload and haven't been able to figure out how to generate this type of query using pgx.

As I understand it when I do,

... WHERE id = ANY($1) 

that generates the following,

WHERE id = ANY('{ x, y, z, ... }')

but I need WHERE id = ANY(VALUES(x), VALUES(y), ...) e.g. https://gist.github.com/alq666/6282672#file-better-query-blog-sql-L8

Any ideas how I can achieve that?

[danielcondemarin]

I managed to craft it manually like this which isn't too bad. Will leave it here in case there is a better way or serves someone

// https://www.datadoghq.com/blog/100x-faster-postgres-performance-by-changing-1-line/
values := make([]string, 0, len(ids))
for i := 1; i <= len(values); i++ {
 values = append(values, fmt.Sprintf("($%d::uuid)", i))
}
valuesList := fmt.Sprintf("VALUES %s", strings.Join(values, ","))

rows, err := c.db.Query(ctx, fmt.Sprintf(`
  SELECT ... WHERE id = ANY(%s)
`, valuesList), ids...)

[danielcondemarin]

The only caveat is I can't use prepared statements

[jackc]

I don't think that technique is useful anymore. See the end of that post:

Update: this change need only be applied on 9.0. As of Postgres 9.3, ANY(ARRAY[...]) is performing properly again.

Regardless though, I don't know of any way to do that technique aside from creating a custom SQL string each time like you are doing.