Interesting slow query - Mailing list pgsql-performance

    Here are two ways to phrase a query... the planner choses very different
plans as you will see. Everything is freshly ANALYZEd.


EXPLAIN ANALYZE SELECT r.* FROM raw_annonces r LEFT JOIN annonces a ON
a.id=r.id LEFT JOIN archive_data d ON d.id=r.id WHERE a.id IS NULL AND
d.id IS NULL AND r.id >1130306 order by id limit 1;
                                                                           QUERY
PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..2.54 rows=1 width=627) (actual time=708.167..708.168
rows=1 loops=1)
    ->  Merge Left Join  (cost=0.00..128497.77 rows=50539 width=627)
(actual time=708.165..708.165 rows=1 loops=1)
          Merge Cond: ("outer".id = "inner".id)
          Filter: ("inner".id IS NULL)
          ->  Merge Left Join  (cost=0.00..27918.92 rows=50539 width=627)
(actual time=144.519..144.519 rows=1 loops=1)
                Merge Cond: ("outer".id = "inner".id)
                Filter: ("inner".id IS NULL)
                ->  Index Scan using raw_annonces_pkey on raw_annonces r
(cost=0.00..11222.32 rows=50539 width=627) (actual time=0.040..0.040
rows=1 loops=1)
                      Index Cond: (id > 1130306)
                ->  Index Scan using annonces_pkey on annonces a
(cost=0.00..16118.96 rows=65376 width=4) (actual time=0.045..133.272
rows=65376 loops=1)
          ->  Index Scan using archive_data_pkey on archive_data d
(cost=0.00..98761.01 rows=474438 width=4) (actual time=0.060..459.995
rows=474438 loops=1)
  Total runtime: 708.316 ms

EXPLAIN ANALYZE SELECT * FROM raw_annonces r WHERE r.id>1130306 AND NOT
EXISTS( SELECT id FROM annonces WHERE id=r.id ) AND NOT EXISTS( SELECT id
 FROM archive_data WHERE id=r.id ) ORDER BY id LIMIT 1;
                                                                     QUERY
PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..38.12 rows=1 width=627) (actual time=0.040..0.041
rows=1 loops=1)
    ->  Index Scan using raw_annonces_pkey on raw_annonces r
(cost=0.00..481652.07 rows=12635 width=627) (actual time=0.039..0.039
rows=1 loops=1)
          Index Cond: (id > 1130306)
          Filter: ((NOT (subplan)) AND (NOT (subplan)))
          SubPlan
            ->  Index Scan using archive_data_pkey on archive_data
(cost=0.00..3.66 rows=1 width=4) (actual time=0.007..0.007 rows=0 loops=1)
                  Index Cond: (id = $0)
            ->  Index Scan using annonces_pkey on annonces
(cost=0.00..5.65 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)
                  Index Cond: (id = $0)
  Total runtime: 0.121 ms


    Ideas ?