HashSetOp

This operation is used by INTERSECT/EXCEPT operations (with optional “ALL" modifier).

It works by running sub-operation of Append for a pair of sub-queries, and then, based on result and optional ALL modifier, it figures which rows should be returned. I haven't digged in the source code so I can't tell you exactly how it works, but given the name and the operation, it looks like a simple counter-based solution.

In here we can see that unlike UNION, these operations work on two sources of data:

Please note that pg_class is scanned only once – line #6. But it's results are stored in “x", and then scanned twice – inside aggregate (line #9) and HashAggregate (10).

How is it different from Materialize? To answer fully, one would need to jump into sources, but I would say that the difference stems from simple fact that CTE's are user defined. While Materialize is helper operation that Pg chooses to use when (it thinks) it makes sense.

The very important thing is that CTEs are ran just as specified. So they can be used to circumvent some not-so-good optimizations that planner normally can do.

InitPlan

This plan happens whenever there is a part of your query that can (or have to) be calculated before anything else, and it doesn't depend on anything in the rest of your query.

For every row that is returned by scan on “pg_class as c", Pg has to run SubPlan, which checks how many rows in pg_class have the same (as currently processed row) value in relkind column.

Please note “loops=295″ in the “Seq Scan on pg_class x" line, and matching “rows=295″ in the earlier “Seq Scan on pg_class c" node.

Other ?

Yes. There are other operations. Some of them are too rare to care about (especially since you do have the great source of knowledge: sources), some are (I suspect) older versions of newer nodes.

If you have a plan with operation I did not cover, and you don't understand it – please let me know in comments – link to explain output on explain.depesz.com, operation name, and Pg version that you have it in. Will comment on such cases with whatever information I might find.