X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id B136F9DC847 for ; Fri, 24 Mar 2006 18:54:43 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 73232-06 for ; Fri, 24 Mar 2006 18:54:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id 878C49DC83F for ; Fri, 24 Mar 2006 18:54:39 -0400 (AST) Received: (qmail 30386 invoked from network); 24 Mar 2006 23:54:39 +0100 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 24 Mar 2006 23:54:39 +0100 Date: Fri, 24 Mar 2006 23:54:37 +0100 To: pgsql-performance@postgresql.org Subject: Query plan from hell References: <4423EACD.80706@krap.dk> <20060324125919.GD90527@pervasive.com> <5090.1143216154@sss.pgh.pa.us> <4424458D.4060106@krap.dk> <8427.1143228229@sss.pgh.pa.us> From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <8427.1143228229@sss.pgh.pa.us> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.112 required=5 tests=[AWL=0.112] X-Spam-Score: 0.112 X-Spam-Level: X-Archive-Number: 200603/513 X-Sequence-Number: 17950 Whoa ! bookmark_delta contains very few rows but is inserted/deleted very often... the effect is spectacular ! I guess I'll have to vacuum analyze this table every minute... annonces=# EXPLAIN ANALYZE SELECT id, priority FROM annonces WHERE id IN (SELECT annonce_id FROM bookmark_delta); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Hash IN Join (cost=32.12..8607.08 rows=1770 width=6) (actual time=387.011..387.569 rows=1 loops=1) Hash Cond: ("outer".id = "inner".annonce_id) -> Seq Scan on annonces (cost=0.00..7796.00 rows=101500 width=6) (actual time=0.022..164.369 rows=101470 loops=1) -> Hash (cost=27.70..27.70 rows=1770 width=4) (actual time=0.013..0.013 rows=5 loops=1) -> Seq Scan on bookmark_delta (cost=0.00..27.70 rows=1770 width=4) (actual time=0.004..0.010 rows=5 loops=1) Total runtime: 387.627 ms (6 lignes) annonces=# EXPLAIN ANALYZE SELECT id, priority FROM annonces a, (SELECT annonce_id FROM bookmark_delta GROUP BY annonce_id) foo WHERE a.id=foo.annonce_id; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=32.12..10409.31 rows=1770 width=6) (actual time=0.081..0.084 rows=1 loops=1) -> HashAggregate (cost=32.12..49.83 rows=1770 width=4) (actual time=0.038..0.040 rows=1 loops=1) -> Seq Scan on bookmark_delta (cost=0.00..27.70 rows=1770 width=4) (actual time=0.024..0.027 rows=5 loops=1) -> Index Scan using annonces_pkey on annonces a (cost=0.00..5.83 rows=1 width=6) (actual time=0.039..0.040 rows=1 loops=1) Index Cond: (a.id = "outer".annonce_id) Total runtime: 0.163 ms (6 lignes) annonces=# vacuum bookmark_delta ; VACUUM annonces=# EXPLAIN ANALYZE SELECT id, priority FROM annonces WHERE id IN (SELECT annonce_id FROM bookmark_delta); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Hash IN Join (cost=32.12..8607.08 rows=1770 width=6) (actual time=195.284..196.063 rows=1 loops=1) Hash Cond: ("outer".id = "inner".annonce_id) -> Seq Scan on annonces (cost=0.00..7796.00 rows=101500 width=6) (actual time=0.014..165.626 rows=101470 loops=1) -> Hash (cost=27.70..27.70 rows=1770 width=4) (actual time=0.008..0.008 rows=2 loops=1) -> Seq Scan on bookmark_delta (cost=0.00..27.70 rows=1770 width=4) (actual time=0.003..0.004 rows=2 loops=1) Total runtime: 196.122 ms (6 lignes) annonces=# vacuum analyze bookmark_delta ; VACUUM annonces=# EXPLAIN ANALYZE SELECT id, priority FROM annonces WHERE id IN (SELECT annonce_id FROM bookmark_delta); QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=1.02..6.88 rows=1 width=6) (actual time=0.025..0.027 rows=1 loops=1) -> HashAggregate (cost=1.02..1.03 rows=1 width=4) (actual time=0.011..0.012 rows=1 loops=1) -> Seq Scan on bookmark_delta (cost=0.00..1.02 rows=2 width=4) (actual time=0.004..0.006 rows=2 loops=1) -> Index Scan using annonces_pkey on annonces (cost=0.00..5.83 rows=1 width=6) (actual time=0.009..0.010 rows=1 loops=1) Index Cond: (annonces.id = "outer".annonce_id) Total runtime: 0.104 ms (6 lignes)