Right now it doesn't know how to do that. It'd be nice if PostgreSQL could automatically recognise a preposterously long IN clause or chain of similar AND conditions and switch to a smarter approach like doing a hashed join or implicitly turning it into a CTE node. parsing the VALUES list took almost no time at all, performing the same or slightly faster than the table approach in most tests. with no element equal to a value in the specified array (for example, , , and so on).What surprised me was how fast doing it with a CTE using a VALUES list was. the field value is not in the specified array or. you can see that there's a truly huge gap between both IN and AND lists vs doing a proper join. Examination of the plan shows that Pg translates NOT IN to ALL.Subquery and join based table exclusion were much the same across repeated runs.AND list generated with SELECT string_agg(item::text, ' AND item ') from exclude ).IN list generated with SELECT 'IN (' || string_agg(item::text, ',' ORDER BY item) || ')' from exclude.making the CTE-based approach over three thousand times faster than the AND list and 130 times faster than the NOT IN list.Ĭode here: (shield your eyes, ye who follow this link).įor this data set size adding an index on the exclusion list made no difference.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |