Re: converting in() clause into a with prefix?

On 10/16/15 1:18 PM, Benjamin Smith wrote:
> I have a horribly-performing query similar to below, and I'd like to convert
> it to use a "WITH mytable as ( ... ) " without having to re-architect my code.
> For some reason, using a WITH prefix seems to generally work much faster than
> IN() sub clause even allowing identical results. (runs in 1/4th the time)

Presumably it's faster because WITH is currently an optimization fence.
Instead of WITH, you could also do

WHERE (classes.school_id, classes.building_id) IN( SELECT a, b FROM
unnest(...) u(a, b))
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com