Interesting slow query

From: PFC <lists(at)peufeu(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Interesting slow query
Date: 2006-06-12 19:42:00
Message-ID: op.ta1s0auacigqcu@apollo13
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 ?

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Zydoon 2006-06-12 21:47:07 Re: scaling up postgres
Previous Message Sven Geisler 2006-06-12 15:33:20 Re: Posrgres speed problem