search

seancorfield / next-jdbc

A modern low-level Clojure wrapper for JDBC-based access to databases.
https://cljdoc.org/d/com.github.seancorfield/next.jdbc/
Eclipse Public License 1.0
768 stars 90 forks source link

Pathlogical performance in pgsql for `WHERE col = ANY(?)` #283

Closed bgrabow closed 3 months ago

bgrabow commented 3 months ago

Describe the bug This is about a performance bug in Postgres itself that is likely to be encountered when following the documentation in next.jdbc.

When filtering query results using WHERE col = ANY(?), if the array of values contains exactly one item the query can encounter pathologically bad performance that takes orders of magnitude longer to complete compared to the same query with more than one item in the array of values. Detailed explanation can be found in this article and this pgsql bug report.

The next.jdbc documentation helpfully suggests the use of WHERE col = ANY(?).

To Reproduce Reproduction steps documented in the pgsql issue.

Expected behavior The next.jdbc documentation should either mention the pathological performance of WHERE col = ANY(?) or suggest another alternative.

Here's one alternative that I'm using to avoid the performance bug:

["SELECT * 
  FROM table
  WHERE col IN (select unnest(array(select jsonb_array_elements_text(?))))"
 vals]

There are probably other/better alternatives too.

seancorfield commented 3 months ago

I don't use PostgreSQL so I rely on the community for handling bugs in that database/driver. Feel free to submit a PR against the documentation.