dataset using IN ? when passed an arrray of 1 argument, which could optimize to = ?

[HoneyryderChuck]

👋 not sure if an issue, so opening the discussion first.

I'm using sequel in a library where a list of ids is select with lock first (via FOR UPDATE); this list is then used to perform 2 operations on the resulting dataset, ds.where(id: ids).update... and ds.where(ids: ids).delete.

In a first version, an id was picked up at a time. I noticed that, for the mentioned operations, the resulting datasets would emit UPDATE (?) FROM table WHERE id IN (?) and DELETE FROM table WHERE id IN (?), which was somewhat suprising to me, but most noticeably was underperforming in the master replica; the production system uses postgresql 14, and it seems that such queries have some performance issues (I read something about improvements for groups in most recent pg 17), and queries should use = ? instead for better performance. In fact, I managed to force it to use = ? and stopped seeing the previous p95 spikes.

I was wondering whether the sequel call for filter could inspect whether the parameter 1) is an array, and 2) is of size 1, and if so, apply the different operator. Is this something that makes sense? Or do you consider out of scope?

[jeremyevans]

It's possible, but should not be necessary. PostgreSQL should optimize it the same way:

EXPLAIN SELECT * FROM generate_series(1,10) AS x WHERE x IN (1);         
                              QUERY PLAN                              
----------------------------------------------------------------------
 Function Scan on generate_series x  (cost=0.00..0.13 rows=1 width=4)
   Filter: (x = 1)

Multiple entries in the array will change the query plan:

EXPLAIN SELECT * FROM generate_series(1,10) AS x WHERE x IN (1,2);
                              QUERY PLAN                              
----------------------------------------------------------------------
 Function Scan on generate_series x  (cost=0.00..0.13 rows=2 width=4)
   Filter: (x = ANY ('{1,2}'::integer[]))

This appears to be the same behavior back to PostgreSQL 8.4.

Maybe there are cases where the database doesn't perform the optimization? If you could provide a self-contained example, that would help. If there are cases it would help, I would open to shipping the feature in a Sequel extension.

It's not a difficult change to make. The pg_auto_parameterize extension uses a similar optimization to change from IN(?, ?, ...) to = ANY(?), but only for cases where the array has more than one entry, to avoid a deoptimization for single entry queries.

[HoneyryderChuck]

I think you're right, and I'm over-analyzing the particular issue ar hand. FWIW in a high write heavy workload, and due to how postgresql inefficiently handles updates for tables with indexes, the queryplanner may find itself in situations where the index is momentarily unsynched due to shared buffers containing the data not having been flushed to disk yet, and may decide to just table scan meanwhile (which is what I'm finding in these periods). This phenomenon is what is known as write amplification, I believe.

I was led to believe that IN queries somehow didn't fully use the index, but after rereading the announcement, the claim is only about improved performance.