Date: Thu, 11 May 2006 16:01:41 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Zeugswetter Andreas DCP SD <ZeugswetterA@spardat.at>
Cc: PFC <lists@peufeu.com>, Greg Stark <gsstark@mit.edu>,
	Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org,
	pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Big IN() clauses etc : feature proposal
Message-ID: <20060511210141.GP99570@pervasive.com>
References: <E1539E0ED7043848906A8FF995BDA57901053F20@m0143.s-mxs.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <E1539E0ED7043848906A8FF995BDA57901053F20@m0143.s-mxs.net>
User-Agent: Mutt/1.5.11

On Thu, May 11, 2006 at 09:55:15AM +0200, Zeugswetter Andreas DCP SD wrote:
> 
> > Something else worth considering is not using the normal 
> > catalog methods
> > for storing information about temp tables, but hacking that together
> > would probably be a rather large task.
> 
> But the timings suggest, that it cannot be the catalogs in the worst
> case
> he showed.
> 
> > 0.101 ms BEGIN
> > 1.451 ms CREATE TEMPORARY TABLE tmp ( a INTEGER NOT NULL, b INTEGER
> NOT  
> > NULL, c TIMESTAMP NOT NULL, d INTEGER NOT NULL ) ON COMMIT DROP
> 
> 1.4 seconds is not great for create table, is that what we expect ?
milliseconds... :) Given the amount of code and locking that it looks
like is involved in creating a table, that might not be unreasonable...

> > 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
> > 
> > 	CREATING the table is OK, but what happens on COMMIT ? I hear
> the disk  
> > seeking frantically.
> 
> 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.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461