From ap@cict.nl Thu Aug 14 11:41:25 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 258C5D1C4D0 for ; Thu, 14 Aug 2003 11:41:25 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 81378-06 for ; Thu, 14 Aug 2003 08:39:49 -0300 (ADT) Received: from mail.cict.nl (vnd-7521.mxs.adsl.euronet.nl [62.234.149.33]) by svr1.postgresql.org (Postfix) with ESMTP id 58234D1C990 for ; Thu, 14 Aug 2003 08:41:13 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886 for ; Thu, 14 Aug 2003 13:41:12 +0200 Message-ID: <00e801c36259$30b908b0$b696a8c0@APR> From: "Alexander Priem" To: References: <002a01c34f62$75c01de0$b696a8c0@APR> Subject: 'View'-performance Date: Thu, 14 Aug 2003 13:42:50 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00E5_01C36269.F424B3C0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=1.5 tagged_above=0.0 required=5.0 tests=HTML_20_30, REFERENCES X-Spam-Level: * X-Archive-Number: 200308/252 X-Sequence-Number: 3069 This is a multi-part message in MIME format. ------=_NextPart_000_00E5_01C36269.F424B3C0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I am wondering about something: I have a table (let's call it "Table") whic= h will contain a lot of records. Every record has a field named "deleted" w= hich can be either NULL or a date value. When this field is NULL, the recor= d in question may be used by a program. If the field contains a date, this = field must be considered as "deleted" and cannot be used anymore. The reason why I don't actually delete such records is that I want to be ab= le to reference them from other locations (for history purposes). What I am thinking about is creating two views for this table: Table_View a= nd Table_History. Table_View would contain all records where "Deleted is nu= ll". Table_History would just contain all records (Select * From Table). In my program most queries would need to view only the records where delete= d is null. Would " Select * from Table_View Where Name=3D'xxx' " perform worse than " = Select * from Table Where deleted is null and Name=3D'xxx' " ? I ask this because I would like it if I wouldn't have to type "where delete= d is null" for about every query in my program. But I will not use this str= ategy if this would mean serious performance loss... Thanks in Advance, Alexander Priem. ------=_NextPart_000_00E5_01C36269.F424B3C0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,
 
I am wondering about something: I have a t= able=20 (let's call it "Table") which will contain a lot of records. Every rec= ord=20 has a field named "deleted" which can be either NULL or a date value. When = this=20 field is NULL, the record in question may be used by a program. If the fiel= d=20 contains a date, this field must be considered as "deleted" and cannot be u= sed=20 anymore.
 
The reason why I don't actually delete suc= h records=20 is that I want to be able to reference them from other locations (for histo= ry=20 purposes).
 
What I am thinking about is creating two v= iews for=20 this table: Table_View and Table_History. Table_View would contain all reco= rds=20 where "Deleted is null". Table_History would just contain all records (Sele= ct *=20 =46rom Table).
 
In my program most queries would need to v= iew only=20 the records where deleted is null.
 
Would " Select * from Table_View Where Nam= e=3D'xxx' "=20 perform worse than " Select * from Table Where deleted is null=20 and Name=3D'xxx' " ?
 
I ask this because I would like it if I wo= uldn't=20 have to type "where deleted is null" for about every query in my program.= =20 But I will not use this strategy if this would mean serious=20 performance loss...
 
Thanks in Advance,
Alexander Priem.
------=_NextPart_000_00E5_01C36269.F424B3C0-- From tgl@sss.pgh.pa.us Thu Aug 14 12:01:08 2003 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 7FF8AD1C487 for ; Thu, 14 Aug 2003 12:01:08 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 85374-10 for ; Thu, 14 Aug 2003 08:59:33 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 76268D1C9C6 for ; Thu, 14 Aug 2003 09:00:57 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h7EC0nSa015714; Thu, 14 Aug 2003 08:00:49 -0400 (EDT) To: "Alexander Priem" Cc: pgsql-performance@postgresql.org Subject: Re: 'View'-performance In-reply-to: <00e801c36259$30b908b0$b696a8c0@APR> References: <002a01c34f62$75c01de0$b696a8c0@APR> <00e801c36259$30b908b0$b696a8c0@APR> Comments: In-reply-to "Alexander Priem" message dated "Thu, 14 Aug 2003 13:42:50 +0200" Date: Thu, 14 Aug 2003 08:00:49 -0400 Message-ID: <15713.1060862449@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/254 X-Sequence-Number: 3071 "Alexander Priem" writes: > What I am thinking about is creating two views for this table: Table_View a= > nd Table_History. Table_View would contain all records where "Deleted is nu= > ll". Table_History would just contain all records (Select * From Table). > Would " Select * from Table_View Where Name=3D'xxx' " perform worse than " = > Select * from Table Where deleted is null and Name=3D'xxx' " ? They'd be exactly the same (modulo a few extra microseconds/milliseconds for the query planner to expand the view definition). regards, tom lane