From: PFC <lists@peufeu.com>
To: Mitchell Skinner <mitch@arctur.us>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
Date: Tue, 09 May 2006 18:38:51 +0200
Message-ID: <op.s9alu1vhcigqcu@apollo13> (raw)
In-Reply-To: <1147190285.32304.43.camel@firebolt>
References: <445F7FF8.6070707@comcast.net>
<1147110142.32369.7.camel@archimedes>
<20060508175013.GY99570@pervasive.com>
<28288.1147111196@sss.pgh.pa.us>
<op.s89zl30wcigqcu@apollo13>
<20060509105214.P90693@vesihiisi.cksoft.de>
<op.s8916gm6cigqcu@apollo13>
<20060509113337.M90693@vesihiisi.cksoft.de>
<op.s893vzcpcigqcu@apollo13>
<20060509103632.GD29652@svana.org>
<op.s897j60qcigqcu@apollo13>
<1147190285.32304.43.camel@firebolt>
> 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
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, mitch@arctur.us, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
In-Reply-To: <op.s9alu1vhcigqcu@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