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 090269DCA8D for ; Thu, 23 Mar 2006 00:15:55 -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 25243-08 for ; Thu, 23 Mar 2006 00:15:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 9E49C9DCB78 for ; Thu, 23 Mar 2006 00:15:52 -0400 (AST) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 9DE8330CDE; Thu, 23 Mar 2006 05:15:50 +0100 (MET) From: "Qingqing Zhou" X-Newsgroups: pgsql.performance Subject: Re: WAL logging of SELECT ... INTO command Date: Wed, 22 Mar 2006 14:20:39 +0800 Organization: Hub.Org Networking Services Lines: 22 Message-ID: References: <20060321122211.GU15742@pervasive.com> <1142973230.24487.501.camel@localhost.localdomain> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.609 required=5 tests=[AWL=-0.272, DATE_IN_PAST_12_24=0.881] X-Spam-Score: 0.609 X-Spam-Level: X-Archive-Number: 200603/465 X-Sequence-Number: 17902 "Simon Riggs" wrote > On Tue, 2006-03-21 at 06:22 -0600, Jim C. Nasby wrote: > > Currently, it appears that SELECT * INTO new_table FROM old_table logs > > each page as it's written to WAL. Is this actually needed? Couldn't the > > database simply log that the SELECT ... INTO statement was executed > > instead? Doing so would likely result in a large performance improvement > > in most installs. Is there no provision for writing anything but data > > page changes (or whole pages) to WAL? > > AFAIK it takes the same code path as CREATE TABLE AS SELECT, which > already does exactly what you suggest (except when using PITR). > As I read, they did take the same code path, but did they "simply log that the SELECT ... INTO statement was executed"? If so, how can we rely on the unreliable content of the old_table to do recovery? Regards, Qingqing