X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 21E2D9FA3CD for ; Mon, 12 Jun 2006 16:41:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 94710-08 for ; Mon, 12 Jun 2006 16:41:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id 4A87E9FA159 for ; Mon, 12 Jun 2006 16:41:07 -0300 (ADT) Received: (qmail 4316 invoked from network); 12 Jun 2006 21:42:01 +0200 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 12 Jun 2006 21:42:01 +0200 To: pgsql-performance@postgresql.org Subject: Interesting slow query References: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com> Message-ID: Date: Mon, 12 Jun 2006 21:42:00 +0200 From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <003601c687ce$7aee7b20$7f00a8c0@kicommunication.com> User-Agent: Opera M2/8.54 (Linux, build 1745) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/141 X-Sequence-Number: 19498 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 ?