2 ms·
I always assumed pg would be smart and use something with a constant lookup time. This[1] is a query plan 9.5 made with a table containing 10,000,000 sequential
by orf 11y ago
I always assumed pg would be smart and use something with a constant lookup time. This[1] is a query plan 9.5 made with a table containing 10,000,000 sequential rows, executing "DELETE WHERE id IN (select * from generate_series(1, 1000000));". Maybe your right, it spends 5.8 seconds on the nested loop. I've only used this for smaller volumes where an extra table might be overkill, but I guess with loads more rows a temporary table is better.
1. http://tatiyants.com/pev/#/plans/plan_1454709615696 http://tatiyants.com/pev/#/plans/plan_1454709615696
- empthought 11y agoPG 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.