From: PFC <lists@peufeu.com>
To: Jim C. Nasby <jnasby@pervasive.com>
To: Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>
Cc: Greg Stark <gsstark@mit.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
Date: Thu, 11 May 2006 23:33:31 +0200
Message-ID: <op.s9eot5xvcigqcu@apollo13> (raw)
In-Reply-To: <20060511210141.GP99570@pervasive.com>
References: <E1539E0ED7043848906A8FF995BDA57901053F20@m0143.s-mxs.net>
<20060511210141.GP99570@pervasive.com>
>> > 0.450 ms INSERT INTO tmp SELECT * FROM bookmarks ORDER BY annonce_id
>> DESC
>> > LIMIT 20
>> > 0.443 ms ANALYZE tmp
>> > 0.365 ms SELECT * FROM tmp
>> > 0.310 ms DROP TABLE tmp
>> > 32.918 ms COMMIT
>> The 32 seconds for commit can hardly be catalog related. It seems the
>> file is
>> fsynced before it is dropped.
>
> I'd hope that wasn't what's happening... is the backend smart enough to
> know not to fsync anything involved with the temp table? ISTM that that
> transaction shouldn't actually be creating any WAL traffic at all.
> Though on the other hand there's no reason that DROP should be in the
> transaction at all; maybe that's gumming things up during the commit.
I included the DROP to make it clear that the time was spent in
COMMITting, not in DROPping the table.
Also, you can't use CREATE TEMP TABLE AS SELECT ... and at the same time
make it ON COMMIT DROP. You have to CREATE and INSERT.
With an ON COMMIT DROP temp table, the global timings are the same wether
or not it is dropped before commit : it is always the COMMIT which takes
all the milliseconds.
I still bet on system catalog updates being the main cause of the time
spent in COMMIT...
(because ANALYZE changes this time)
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, jnasby@pervasive.com, ZeugswetterA@spardat.at, gsstark@mit.edu, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
In-Reply-To: <op.s9eot5xvcigqcu@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