From: PFC <lists@peufeu.com>
To: pgsql-performance@postgresql.org
Subject: Interesting slow query
Date: Mon, 12 Jun 2006 21:42:00 +0200
Message-ID: <op.ta1s0auacigqcu@apollo13> (raw)
In-Reply-To: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com>
References: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com>
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 ?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: lists@peufeu.com
Subject: Re: Interesting slow query
In-Reply-To: <op.ta1s0auacigqcu@apollo13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox