Interesting slow query - Mailing list pgsql-performance

From PFC
Subject Interesting slow query
Date
Msg-id op.ta1s0auacigqcu@apollo13
Whole thread Raw
In response to Re: Bulk loading/merging  ("Ahmad Fajar" <ahmadfajar@i2.co.id>)
Responses Re: Interesting slow query  (Tom Lane <tgl@sss.pgh.pa.us>)
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 ?

pgsql-performance by date:

Previous
From: Sven Geisler
Date:
Subject: Re: Posrgres speed problem
Next
From: Tom Lane
Date:
Subject: Re: Interesting slow query