X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id EDF7D9F931E for ; Tue, 9 May 2006 13:37:43 -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 89887-01 for ; Tue, 9 May 2006 13:37:33 -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 3BE6F9FA2F8 for ; Tue, 9 May 2006 13:37:31 -0300 (ADT) Received: (qmail 6097 invoked from network); 9 May 2006 18:38:52 +0200 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 9 May 2006 18:38:52 +0200 Date: Tue, 09 May 2006 18:38:51 +0200 To: "Mitchell Skinner" Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal Cc: pgsql-performance@postgresql.org, pgsql-hackers@postgresql.org References: <445F7FF8.6070707@comcast.net> <1147110142.32369.7.camel@archimedes> <20060508175013.GY99570@pervasive.com> <28288.1147111196@sss.pgh.pa.us> <20060509105214.P90693@vesihiisi.cksoft.de> <20060509113337.M90693@vesihiisi.cksoft.de> <20060509103632.GD29652@svana.org> <1147190285.32304.43.camel@firebolt> 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: <1147190285.32304.43.camel@firebolt> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/185 X-Sequence-Number: 18972 > Does the time for commit change much if you leave out the analyze? Yes, when I don't ANALYZE the temp table, commit time changes from 30 ms to about 15 ms ; but the queries get horrible plans (see below) : Fun thing is, the rowcount from a temp table (which is the problem here) should be available without ANALYZE ; as the temp table is not concurrent, it would be simple to inc/decrement a counter on INSERT/DELETE... I like the temp table approach : it can replace a large, complex query with a batch of smaller and easier to optimize queries... EXPLAIN ANALYZE SELECT a.* FROM tmp t, annonces_display a WHERE a.id=t.id ORDER BY t.sort; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=3689.88..3693.15 rows=1310 width=940) (actual time=62.327..62.332 rows=85 loops=1) Sort Key: t.sort -> Merge Join (cost=90.93..3622.05 rows=1310 width=940) (actual time=5.595..61.373 rows=85 loops=1) Merge Cond: ("outer".id = "inner".id) -> Index Scan using annonces_pkey on annonces (cost=0.00..3451.39 rows=10933 width=932) (actual time=0.012..6.620 rows=10916 loops=1) -> Sort (cost=90.93..94.20 rows=1310 width=12) (actual time=0.098..0.105 rows=85 loops=1) Sort Key: t.id -> Seq Scan on tmp t (cost=0.00..23.10 rows=1310 width=12) (actual time=0.004..0.037 rows=85 loops=1) Total runtime: 62.593 ms EXPLAIN ANALYZE SELECT * FROM contacts WHERE id IN (SELECT contact_id FROM tmp); QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Hash Join (cost=28.88..427.82 rows=200 width=336) (actual time=0.156..5.019 rows=45 loops=1) Hash Cond: ("outer".id = "inner".contact_id) -> Seq Scan on contacts (cost=0.00..349.96 rows=9396 width=336) (actual time=0.009..3.373 rows=9396 loops=1) -> Hash (cost=28.38..28.38 rows=200 width=4) (actual time=0.082..0.082 rows=46 loops=1) -> HashAggregate (cost=26.38..28.38 rows=200 width=4) (actual time=0.053..0.064 rows=46 loops=1) -> Seq Scan on tmp (cost=0.00..23.10 rows=1310 width=4) (actual time=0.001..0.015 rows=85 loops=1) Total runtime: 5.092 ms ANALYZE tmp; ANALYZE annonces=> EXPLAIN ANALYZE SELECT a.* FROM tmp t, annonces_display a WHERE a.id=t.id ORDER BY t.sort; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=508.63..508.84 rows=85 width=940) (actual time=1.830..1.832 rows=85 loops=1) Sort Key: t.sort -> Nested Loop (cost=0.00..505.91 rows=85 width=940) (actual time=0.040..1.188 rows=85 loops=1) -> Seq Scan on tmp t (cost=0.00..1.85 rows=85 width=12) (actual time=0.003..0.029 rows=85 loops=1) -> Index Scan using annonces_pkey on annonces (cost=0.00..5.89 rows=1 width=932) (actual time=0.003..0.004 rows=1 loops=85) Index Cond: (annonces.id = "outer".id) Total runtime: 2.053 ms (7 lignes) annonces=> EXPLAIN ANALYZE SELECT * FROM contacts WHERE id IN (SELECT contact_id FROM tmp); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=2.06..139.98 rows=36 width=336) (actual time=0.072..0.274 rows=45 loops=1) -> HashAggregate (cost=2.06..2.51 rows=45 width=4) (actual time=0.052..0.065 rows=46 loops=1) -> Seq Scan on tmp (cost=0.00..1.85 rows=85 width=4) (actual time=0.003..0.016 rows=85 loops=1) -> Index Scan using contacts_pkey on contacts (cost=0.00..3.04 rows=1 width=336) (actual time=0.003..0.004 rows=1 loops=46) Index Cond: (contacts.id = "outer".contact_id) Total runtime: 0.341 ms