Re:Creating a Document-Store Hybrid in Postgres 9.5
by empthought on Friday February 05, 2016 @05:35PM
(#11045189)
PG is doing the smart thing; it's optimizing for the common case of short IN/NOT IN literals in SQL queries. Simple arrays have much better constant factors.
If the clause is a subselect (like yours) and is also an actual table (unlike yours), it will be smart and rewrite it as a JOIN, which will generally have better complexity if the indexes are correct.
1. http://tatiyants.com/pev/#/plans/plan_1454709615696