Chances are, though, there is a more efficient (albeit more verbose) query to achieve the same. Your original approach being a hot contender.

It's typically more efficient to filter rows early. The simple predicate above filters after joining all relations, producing a (much) bigger transient set, which is typically more expensive.

For all I know after your question update,this might be the fastest query:

We are leaving the domain of simple questions in a public forum and enter the territory of paid consulting work here ...

The main challenge with this kind of query is that the many joins can produce an exorbitant number of rows (before filtering). It's typically more efficient to filter rows early. The simple predicate above filters after joining all relations. Chances are, there is a more efficient (albeit more verbose) query. Your original approach being a hot contender.

For all I know, after your question update, this might be the faster:

Depending on cardinalities and what exactly is in your filters, there may be room for more optimization. But we have well left the domain of simple questions in a public forum and entered into the territory of paid consulting work ...

Chances are, though, there is a more efficient (albeit more verbose) query to achieve the same. Your original approach being a hot contender. You would have to share details for that.

It's typically more efficient to filter rows early. My simple predicate filters after joining all relations, producing a (much) bigger transient set, which is typically more expensive.

Short and simple

Or, while you know the number of columns, the simpler version you found yourself:

WHERE # uniq(sort(ARRAY[s1, s2, s3, s4, s5, s6, s7, s8])) = 8

Fast

Chances are, though, there is a more efficient (albeit more verbose) query to achieve the same. Your original approach being a hot contender.

It's typically more efficient to filter rows early. The simple predicate above filters after joining all relations, producing a (much) bigger transient set, which is typically more expensive.

For all I know after your question update,this might be the fastest query:

SELECT id, s1, s2, s3, s4, s5, s6, s7, s8
FROM  (
   SELECT superobject_id AS id, path, set AS s1
   FROM   superobject__object JOIN object o ON o.id = s.object_id AND <filter_1>)
   ) s1
JOIN  (
   SELECT superobject_id AS id, path, set AS s2
   FROM   superobject__object JOIN object o ON o.id = s.object_id AND <filter_2>)
   ) s2 USING (id, path)
...
JOIN  (
   SELECT superobject_id AS id, path, set AS s8
   FROM   superobject__object JOIN object o ON o.id = s.object_id AND <filter_8>)
   ) s8 USING (id, path)
WHERE  s2 <> s1
AND    s3 NOT IN (s1, s2)
...
AND    s8 NOT IN (s1, s2, s3, s4, s5, s6, s7);

Or still:

...
WHERE # uniq(sort(ARRAY[s1, s2, s3, s4, s5, s6, s7, s8])) = 8

The most substantial change versus your version is that I replaced all object_id IN (subquery) clauses with joins. The rationale being what I happened to post just yesterday:

• When using a CTE, why do I need to use SELECT twice in order to filter on values from an auxiliary statement?

With then 16 tables in the FROM clause we now surpass the default of 8 in join_collapse_limit from_collapse_limit . Past that limit Postgres stops trying to flatten all join items and evaluate every possible order of joins (because the number of combinations gets out of hand and planning becomes too expensive). So it becomes increasingly important to move the most selective filters to the top of the FROM clause. Read this chapter in the manual for details:

• "Controlling the Planner with Explicit JOIN Clauses"

Either selectivity is hard to predict for you and Postgres typically comes up with better estimates based on available statistics. That can't work perfectly, but still typically better than manual intervention.

Or you know better which filters are most selective. Then you'll want to define the order or joins manually to get the best plan and save on planning time.

I rearranged the FROM clause into 8 subqueries. Now you can play with the two mentioned settings to optimize query plans (and planning time). You'll want to set these locally, like:

BEGIN;
SET LOCAL from_collapse_limit = 1;
SET LOCAL join_collapse_limit = 1;

SELECT ...

ROLLBACK;  -- or COMMIT;

The USING clause mainly shortens the syntax. You may as well spell it out with ON.

Also important: I start with s2 <> s1 not s1 NOT IN (s2, s3, s4, s5, s6, s7, s8) to stay in sync with the order of joins. But that may constrain the order of FROM items. So while the best order of joins is unclear, the alternative short filter may still be better.

We are leaving the domain of simple questions in a public forum and enter the territory of paid consulting work here ...

Related:

• Complex view becomes slow when adding ORDER BY in outer query with small LIMIT
• Postgres JOIN conditions vs WHERE conditions