Why are correlated subqueries sometimes faster than joins in Postgres?
To start, a correlated subquery can't return more than one row. Take for instance.

SELECT t.x
FROM ( VALUES (1),(2) )
  AS t(x)
LEFT OUTER JOIN (
  VALUES (1),(1)
) AS y(x)
USING (x);
 x 
---
 1
 1
 2
(3 rows)


Versus the same query written as a correlated subquery

SELECT t.x, (
  SELECT *
  FROM ( VALUES (1),(1) )
    AS y(x)
  WHERE y.x = t.x
)
FROM ( VALUES (1),(2) )
  AS t(x);
ERROR:  more than one row returned by a subquery used as an expression


That tells the planner something useful, and it can optimize that. Adjusting for this, by reducing the inner table to one row -- you'll see the plans:

EXPLAIN ANALYZE SELECT t.x
FROM ( VALUES (1),(2) )
  AS t(x)
LEFT OUTER JOIN (
  VALUES (1),(1)
) AS y(x)
USING (x);

                                                     QUERY PLAN                                                      
---------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=0.05..0.10 rows=2 width=4) (actual time=0.019..0.021 rows=3 loops=1)
   Hash Cond: ("*VALUES*".column1 = "*VALUES*_1".column1)
   -&gt;  Values Scan on "*VALUES*"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.002..0.003 rows=2 loops=1)
   -&gt;  Hash  (cost=0.03..0.03 rows=2 width=4) (actual time=0.005..0.005 rows=2 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         -&gt;  Values Scan on "*VALUES*_1"  (cost=0.00..0.03 rows=2 width=4) (actual time=0.001..0.003 rows=2 loops=1)
 Planning time: 0.107 ms
 Execution time: 0.046 ms
(8 rows)


Correlated subquery method.

EXPLAIN ANALYZE SELECT t.x, (
  SELECT *
  FROM ( VALUES (1) )
    AS y(x)
  WHERE y.x = t.x
)        
FROM ( VALUES (1),(2) )
  AS t(x);


                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Values Scan on "*VALUES*"  (cost=0.00..0.05 rows=2 width=4) (actual time=0.014..0.017 rows=2 loops=1)
   SubPlan 1
     -&gt;  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.004 rows=0 loops=2)
           One-Time Filter: (1 = "*VALUES*".column1)
 Planning time: 0.097 ms
 Execution time: 0.043 ms
(6 rows)


This follows vaguely your above examples. So when is a correlated subquery better than a join? Not sure, but the correlated subquery gets to skip the hash join.

You have in your plan this

Filter: ((id_site IS NULL) AND ("labelDate" &lt; '2015-09-01'::date))


That's not even the same SQL you've got above. That makes me call into question the whole thing.

I would do this though

CREATE INDEX ON pages ( labelDate ) WHERE id_site IS NULL;


If you don't have an index on id_site IS NULL or id_site then it seems likely you're just forcing a seq scan there.