From ap@cict.nl Fri Aug 29 06:50:47 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 CD38ED1B8B3 for ; Fri, 29 Aug 2003 06:50:47 +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 92533-09 for ; Fri, 29 Aug 2003 03:50:16 -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 230E1D1B897 for ; Fri, 29 Aug 2003 03:50:14 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886 for ; Fri, 29 Aug 2003 08:50:12 +0200 Message-ID: <012f01c36dfa$0f4a8760$b696a8c0@APR> From: "Alexander Priem" To: Subject: Indexing question Date: Fri, 29 Aug 2003 08:52:06 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_012C_01C36E0A.D2C8A900" 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.2 tagged_above=0.0 required=5.0 tests=HTML_30_40, HTML_FONT_BIG, HTML_FONT_COLOR_BLUE, HTML_FONT_FACE_ODD X-Spam-Level: * X-Archive-Number: 200308/469 X-Sequence-Number: 3285 This is a multi-part message in MIME format. ------=_NextPart_000_012C_01C36E0A.D2C8A900 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, I have some tables (which can get pretty large) in which I want to record '= current' data as well as 'historical' data. This table has fields 'deleted'= and 'deleteddate' (among other fields, of course). The field 'deleted' is = false be default. Every record that I want to delete gets the value true fo= r 'deleted' and 'deleteddate' is set to the date of deletion. Since these tables are used a lot by queries that only use 'current' data, = I have created a view with a where clause 'Where not deleted'. Also, I have= indexed field 'deleted'. I did this this because I read somewhere that fields that can contain NULL = values will NOT be indexed. Is this true? Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL by = default and to a DATE in the case of a deleted record? I could then index t= he field 'deleteddate' and create a view with where clause 'Where deletedda= te is null'. Would this give the same performance as my current solution (with an indexe= d boolean field 'deleted') ? I cannot test this myself at the moment as I am still in a design phase and= do not have a real server available yet... Thanks in advance, Alexander Priem ------=_NextPart_000_012C_01C36E0A.D2C8A900 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi all,
 
I have some tables (which can get pretty l= arge) in=20 which I want to record 'current' data as well as 'historical' data. Th= is=20 table has fields 'deleted' and 'deleteddate' (among other fields, of course= ).=20 The field 'deleted' is false be default. Every record that I want to delete= gets=20 the value true for 'deleted' and 'deleteddate' is set to the date of=20 deletion.
 
Since these tables are used a lot by queri= es that=20 only use 'current' data, I have created a view with a where clause 'Where n= ot=20 deleted'. Also, I have indexed field 'deleted'.
 
I did this this because I read somewhere t= hat=20 fields that can contain NULL values will NOT be indexed.
 
Is this true?
 
Or could I ditch the 'deleted' field and j= ust set=20 'deleteddate' to NULL by default and to a DATE in the case of a deleted rec= ord?=20 I could then index the field 'deleteddate' and create a view with where cla= use=20 'Where deleteddate is null'.
 
Would this give the same performance as my= current=20 solution (with an indexed boolean field 'deleted') ?
 
I cannot test this myself at the moment as= I am=20 still in a design phase and do not have a real server available=20 yet...
 
Thanks in advance,

Alexander Priem
------=_NextPart_000_012C_01C36E0A.D2C8A900-- From jasiek@klaster.net Fri Aug 29 07:04:12 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 D4939D1B8E6 for ; Fri, 29 Aug 2003 07:04:12 +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 96039-07 for ; Fri, 29 Aug 2003 04:03:43 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id F2BC6D1B8CC for ; Fri, 29 Aug 2003 04:03:39 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 393772B3A4; Fri, 29 Aug 2003 09:03:28 +0200 (CEST) Message-ID: <3F4EFAB6.7030104@klaster.net> Date: Fri, 29 Aug 2003 09:03:18 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5a) Gecko/20030718 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Alexander Priem Cc: pgsql-performance@postgresql.org Subject: Re: Indexing question References: <012f01c36dfa$0f4a8760$b696a8c0@APR> In-Reply-To: <012f01c36dfa$0f4a8760$b696a8c0@APR> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/472 X-Sequence-Number: 3288 > Hi all, > > I have some tables (which can get pretty large) in which I want to > record 'current' data as well as 'historical' data. This table has > fields 'deleted' and 'deleteddate' (among other fields, of course). The > field 'deleted' is false be default. Every record that I want to delete > gets the value true for 'deleted' and 'deleteddate' is set to the date > of deletion. > > Since these tables are used a lot by queries that only use 'current' > data, I have created a view with a where clause 'Where not deleted'. > Also, I have indexed field 'deleted'. I think the best choice for your case is using conditional indexes. It should be much better than indexing 'deleted' field. I don't know on which exactly fields you have to create this index - you have to check it by yourself - what do you have in "where" clause? Example: create index some_index on your_table(id_field) where not deleted; Regards, Tomasz Myrta From chriskl@familyhealth.com.au Fri Aug 29 07:03:07 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 607B7D1B8B3 for ; Fri, 29 Aug 2003 07:03:07 +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 94917-07 for ; Fri, 29 Aug 2003 04:02:36 -0300 (ADT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by svr1.postgresql.org (Postfix) with ESMTP id ABCD6D1B8CC for ; Fri, 29 Aug 2003 04:02:32 -0300 (ADT) Received: from mars (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9/8.12.9) with SMTP id h7T72LLt040433; Fri, 29 Aug 2003 15:02:21 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <0b5e01c36dfb$fbaca380$2800a8c0@mars> From: "Christopher Kings-Lynne" To: "Alexander Priem" , References: <012f01c36dfa$0f4a8760$b696a8c0@APR> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 15:05:52 +0800 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0B5B_01C36E3F.09C3FCA0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=BAYES_30, HTML_30_40, HTML_FONT_BIG, HTML_FONT_COLOR_BLUE, HTML_FONT_FACE_ODD, REFERENCES X-Spam-Level: X-Archive-Number: 200308/471 X-Sequence-Number: 3287 This is a multi-part message in MIME format. ------=_NextPart_000_0B5B_01C36E3F.09C3FCA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Remember to consider partial indexes: eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT NULL Chris ----- Original Message -----=20 From: Alexander Priem=20 To: pgsql-performance@postgresql.org=20 Sent: Friday, August 29, 2003 2:52 PM Subject: [PERFORM] Indexing question Hi all, I have some tables (which can get pretty large) in which I want to record= 'current' data as well as 'historical' data. This table has fields 'delete= d' and 'deleteddate' (among other fields, of course). The field 'deleted' i= s false be default. Every record that I want to delete gets the value true = for 'deleted' and 'deleteddate' is set to the date of deletion. Since these tables are used a lot by queries that only use 'current' data= , I have created a view with a where clause 'Where not deleted'. Also, I ha= ve indexed field 'deleted'. I did this this because I read somewhere that fields that can contain NUL= L values will NOT be indexed. Is this true? Or could I ditch the 'deleted' field and just set 'deleteddate' to NULL b= y default and to a DATE in the case of a deleted record? I could then index= the field 'deleteddate' and create a view with where clause 'Where deleted= date is null'. Would this give the same performance as my current solution (with an inde= xed boolean field 'deleted') ? I cannot test this myself at the moment as I am still in a design phase a= nd do not have a real server available yet... Thanks in advance, Alexander Priem ------=_NextPart_000_0B5B_01C36E3F.09C3FCA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Remember to consider partial indexes:
 
eg. CREATE INDEX ON table (col) WHERE deletedate IS NOT= =20 NULL
 
Chris
----- Original Message -----
Fro= m:=20 Alexander Priem
To: pgsql-performance@postgr= esql.org=20
Sent: Friday, August 29, 2003 2:52= =20 PM
Subject: [PERFORM] Indexing=20 question

Hi all,
 
I have some tables (which can get pretty= large)=20 in which I want to record 'current' data as well as 'historical' dat= a.=20 This table has fields 'deleted' and 'deleteddate' (among other fields, of= =20 course). The field 'deleted' is false be default. Every record that I wan= t to=20 delete gets the value true for 'deleted' and 'deleteddate' is set to the = date=20 of deletion.
 
Since these tables are used a lot by que= ries that=20 only use 'current' data, I have created a view with a where clause 'Where= not=20 deleted'. Also, I have indexed field 'deleted'.
 
I did this this because I read somewhere= that=20 fields that can contain NULL values will NOT be indexed.
 
Is this true?
 
Or could I ditch the 'deleted' field and= just set=20 'deleteddate' to NULL by default and to a DATE in the case of a deleted= =20 record? I could then index the field 'deleteddate' and create a view with= =20 where clause 'Where deleteddate is null'.
 
Would this give the same performance as = my=20 current solution (with an indexed boolean field 'deleted') ?<= /DIV>
 
I cannot test this myself at the moment = as I am=20 still in a design phase and do not have a real server available=20 yet...
 
Thanks in advance,

Alexander Priem
------=_NextPart_000_0B5B_01C36E3F.09C3FCA0-- From ap@cict.nl Fri Aug 29 07:47:47 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 EE363D1B8AB for ; Fri, 29 Aug 2003 07:47:47 +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 97105-09 for ; Fri, 29 Aug 2003 04:47:38 -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 32D49D1B8A4 for ; Fri, 29 Aug 2003 04:47:36 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886; Fri, 29 Aug 2003 09:47:35 +0200 Message-ID: <015f01c36e02$134fe960$b696a8c0@APR> From: "Alexander Priem" To: "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 09:49:28 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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-Archive-Number: 200308/475 X-Sequence-Number: 3291 So if I understand correctly I could ditch the 'deleted' field entirely and use just the 'deleteddate' field. This 'deleteddate' field would be NULL by default. It would contain a date value if the record is considered 'deleted'. The index would be 'create index a on tablename(deleteddate) where deleteddate is null'. I could then access 'current' records with a view like 'create view x_view as select * from tablename where deleteddate is null'. Is that correct? This would be the best performing solution for this kind of thing, I think (theoretically at least)? Kind regards, Alexander Priem. ----- Original Message ----- From: "Tomasz Myrta" To: "Alexander Priem" Cc: Sent: Friday, August 29, 2003 9:03 AM Subject: Re: [PERFORM] Indexing question > > Hi all, > > > > I have some tables (which can get pretty large) in which I want to > > record 'current' data as well as 'historical' data. This table has > > fields 'deleted' and 'deleteddate' (among other fields, of course). The > > field 'deleted' is false be default. Every record that I want to delete > > gets the value true for 'deleted' and 'deleteddate' is set to the date > > of deletion. > > > > Since these tables are used a lot by queries that only use 'current' > > data, I have created a view with a where clause 'Where not deleted'. > > Also, I have indexed field 'deleted'. > > > I think the best choice for your case is using conditional indexes. It > should be much better than indexing 'deleted' field. I don't know on > which exactly fields you have to create this index - you have to check > it by yourself - what do you have in "where" clause? > > Example: > create index some_index on your_table(id_field) where not deleted; > > > Regards, > Tomasz Myrta > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend From jasiek@klaster.net Fri Aug 29 07:59:42 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 B0CB1D1B8B1 for ; Fri, 29 Aug 2003 07:59:42 +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 00480-04 for ; Fri, 29 Aug 2003 04:59:12 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id 3EB3BD1B9AB for ; Fri, 29 Aug 2003 04:59:10 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 5D5AD2B3A4; Fri, 29 Aug 2003 09:58:59 +0200 (CEST) Message-ID: <3F4F07CE.6090803@klaster.net> Date: Fri, 29 Aug 2003 09:59:10 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5a) Gecko/20030718 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Alexander Priem Cc: pgsql-performance@postgresql.org Subject: Re: Indexing question References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> In-Reply-To: <015f01c36e02$134fe960$b696a8c0@APR> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/477 X-Sequence-Number: 3293 > So if I understand correctly I could ditch the 'deleted' field entirely and > use just the 'deleteddate' field. This 'deleteddate' field would be NULL by > default. It would contain a date value if the record is considered > 'deleted'. > > The index would be 'create index a on tablename(deleteddate) where > deleteddate is null'. > > I could then access 'current' records with a view like 'create view x_view > as select * from tablename where deleteddate is null'. > > Is that correct? This would be the best performing solution for this kind of > thing, I think (theoretically at least)? > > Kind regards, > Alexander Priem. Near, but not exactly. You don't need field deleted - it's true. Your example: create index a on tablename(deleteddate) where deleteddate is null we can translate to: create index a on tablename(NULL) where deleteddate is null which doesn't make too much sense. Check your queries. You probably have something like this: select * from tablename where not deleted and xxx Create your index to match xxx clause - if xxx is "some_id=13", then create your index as: create index on tablename(some_id) where deleteddate is null; Regards, Tomasz Myrta From chriskl@familyhealth.com.au Fri Aug 29 08:10:56 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 CE332D1DA60 for ; Fri, 29 Aug 2003 08:10:56 +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 98438-10 for ; Fri, 29 Aug 2003 05:10:48 -0300 (ADT) Received: from houston.familyhealth.com.au (unknown [203.59.48.253]) by svr1.postgresql.org (Postfix) with ESMTP id 615CFD1DA5F for ; Fri, 29 Aug 2003 05:10:44 -0300 (ADT) Received: from mars (work-40.internal [192.168.0.40]) by houston.familyhealth.com.au (8.12.9/8.12.9) with SMTP id h7T8AWLt041701; Fri, 29 Aug 2003 16:10:32 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Message-ID: <0be001c36e05$83034fb0$2800a8c0@mars> From: "Christopher Kings-Lynne" To: "Alexander Priem" , "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 16:14:04 +0800 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.5 tagged_above=0.0 required=5.0 tests=QUOTED_EMAIL_TEXT, RCVD_IN_OSIRUSOFT_COM, REFERENCES X-Spam-Level: X-Archive-Number: 200308/479 X-Sequence-Number: 3295 > So if I understand correctly I could ditch the 'deleted' field entirely and > use just the 'deleteddate' field. This 'deleteddate' field would be NULL by > default. It would contain a date value if the record is considered > 'deleted'. > > The index would be 'create index a on tablename(deleteddate) where > deleteddate is null'. > > I could then access 'current' records with a view like 'create view x_view > as select * from tablename where deleteddate is null'. > > Is that correct? This would be the best performing solution for this kind of > thing, I think (theoretically at least)? Yes, I think it would be best. Definitely better than your current solution. Cheers, Chris From ap@cict.nl Fri Aug 29 08:39:23 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 A422DD1B8AB for ; Fri, 29 Aug 2003 08:39:23 +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 01251-09 for ; Fri, 29 Aug 2003 05:39:14 -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 00DC2D1B8B3 for ; Fri, 29 Aug 2003 05:39:12 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886; Fri, 29 Aug 2003 10:39:11 +0200 Message-ID: <018c01c36e09$4893e980$b696a8c0@APR> From: "Alexander Priem" To: "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 10:41:04 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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-Archive-Number: 200308/484 X-Sequence-Number: 3300 I think I understand what you mean :) Let's see if that's true : The entire table WAS like this: (just one example table, I have many more) create table orderadvice ( orad_id serial primary key, orad_name varchar(25) unique not null, orad_description varchar(50) default null, orad_value integer not null default 0, orad_value_quan integer references quantity (quan_id) not null default 0, orad_deleted boolean not null default false, orad_deleteddate date default null, orad_deletedby integer references systemuser (user_id) default null ) without oids; Indexes were like this: create index orad_deleted_index on orderadvice (orad_deleted); (orad_id and orad_name indexed implicitly in the create table statement) A view on this table: create view orderadvice_edit as select orad_id,orad_name,orad_description,orad_value,orad_value_quan from orderadvice where not orad_deleted; Most queries on this view would be like 'select * from orderadvice_edit where orad_id=100' or 'select * from orderadvice_edit order by orad_name'. How about the following script. Would it be better, given the type of queries that would take place on this table? create table orderadvice ( orad_id serial primary key, orad_name varchar(25) not null, orad_description varchar(50) default null, orad_value integer not null default 0, orad_value_quan integer references quantity (quan_id) not null default 0, orad_deleteddate date default null, orad_deletedby integer references systemuser (user_id) default null ) without oids; create index orad_id_index on orderadvice (orad_id) where orad_deleteddate is null; create index orad_name_index on orderadvice (orad_name) where orad_deleteddate is null; create view orderadvice_edit as select orad_id,orad_name,orad_description,orad_value,orad_value_quan from orderadvice where orad_deleteddate is null; Would queries like 'select * from orderadvice_edit where orad_id=100' or 'select * from orderadvice_edit order by orad_name' both use one of these two partial indexes, given enough records are present in the table? There would be a double index on the primary key this way, right? Thanks for your advice so far, Alexander Priem. ----- Original Message ----- From: "Tomasz Myrta" To: "Alexander Priem" Cc: Sent: Friday, August 29, 2003 9:59 AM Subject: Re: [PERFORM] Indexing question > > So if I understand correctly I could ditch the 'deleted' field entirely and > > use just the 'deleteddate' field. This 'deleteddate' field would be NULL by > > default. It would contain a date value if the record is considered > > 'deleted'. > > > > The index would be 'create index a on tablename(deleteddate) where > > deleteddate is null'. > > > > I could then access 'current' records with a view like 'create view x_view > > as select * from tablename where deleteddate is null'. > > > > Is that correct? This would be the best performing solution for this kind of > > thing, I think (theoretically at least)? > > > > Kind regards, > > Alexander Priem. > > Near, but not exactly. You don't need field deleted - it's true. > > Your example: > create index a on tablename(deleteddate) where deleteddate is null > we can translate to: > create index a on tablename(NULL) where deleteddate is null > which doesn't make too much sense. > > Check your queries. You probably have something like this: > select * from tablename where not deleted and xxx > > Create your index to match xxx clause - if xxx is "some_id=13", then > create your index as: > create index on tablename(some_id) where deleteddate is null; > > Regards, > Tomasz Myrta > From jasiek@klaster.net Fri Aug 29 08:57:48 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 36B48D1B574 for ; Fri, 29 Aug 2003 08:57:48 +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 06763-01 for ; Fri, 29 Aug 2003 05:57:39 -0300 (ADT) Received: from serwer.skawsoft.com.pl (serwer.skawsoft.com.pl [213.25.37.66]) by svr1.postgresql.org (Postfix) with ESMTP id 7C24BD1B8AE for ; Fri, 29 Aug 2003 05:57:36 -0300 (ADT) Received: from klaster.net (core-1.citynet.pl [80.48.135.69]) by serwer.skawsoft.com.pl (Postfix) with ESMTP id 508502B3A4; Fri, 29 Aug 2003 10:57:15 +0200 (CEST) Message-ID: <3F4F1575.8090600@klaster.net> Date: Fri, 29 Aug 2003 10:57:25 +0200 From: Tomasz Myrta User-Agent: Mozilla/5.0 (Windows; U; Win 9x 4.90; pl-PL; rv:1.5a) Gecko/20030718 X-Accept-Language: pl, en-us, en MIME-Version: 1.0 To: Alexander Priem Cc: pgsql-performance@postgresql.org Subject: Re: Indexing question References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> <018c01c36e09$4893e980$b696a8c0@APR> In-Reply-To: <018c01c36e09$4893e980$b696a8c0@APR> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/485 X-Sequence-Number: 3301 > create index orad_id_index on orderadvice (orad_id) where orad_deleteddate > is null; > create index orad_name_index on orderadvice (orad_name) where > orad_deleteddate is null; > > create view orderadvice_edit as select > orad_id,orad_name,orad_description,orad_value,orad_value_quan from > orderadvice where orad_deleteddate is null; > > Would queries like 'select * from orderadvice_edit where orad_id=100' or > 'select * from orderadvice_edit order by orad_name' both use one of these > two partial indexes, given enough records are present in the table? > > There would be a double index on the primary key this way, right? It looks much better now. I'm not sure about the second index. Probably it will be useless, because you sort ALL records with deleteddtata is null. Maybe the first index will be enough. I'm not sure what to do with doubled index on a primary key field. Regards, Tomasz Myrta From ap@cict.nl Fri Aug 29 09:07:02 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 448B2D1B8E5 for ; Fri, 29 Aug 2003 09:07:02 +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 05293-05 for ; Fri, 29 Aug 2003 06:06:32 -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 70D23D1B8D2 for ; Fri, 29 Aug 2003 06:06:29 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886; Fri, 29 Aug 2003 11:06:28 +0200 Message-ID: <01bf01c36e0d$18769280$b696a8c0@APR> From: "Alexander Priem" To: "Tomasz Myrta" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> <018c01c36e09$4893e980$b696a8c0@APR> <3F4F1575.8090600@klaster.net> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 11:08:21 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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-Archive-Number: 200308/486 X-Sequence-Number: 3302 The first index is for sorting on orad_id, the second one for sorting on orad_name. The first one would be useful for queries like 'select * from orderadvice_edit where orad_id=100', the second one for queries like 'select * from orderadvice_edit order by orad_name'. Right? Does anyone know whether it is bad practise to have two indexes on the primary key of a table? (one 'primary key' index and one partial index) ----- Original Message ----- From: "Tomasz Myrta" To: "Alexander Priem" Cc: Sent: Friday, August 29, 2003 10:57 AM Subject: Re: [PERFORM] Indexing question > > create index orad_id_index on orderadvice (orad_id) where orad_deleteddate > > is null; > > create index orad_name_index on orderadvice (orad_name) where > > orad_deleteddate is null; > > > > create view orderadvice_edit as select > > orad_id,orad_name,orad_description,orad_value,orad_value_quan from > > orderadvice where orad_deleteddate is null; > > > > Would queries like 'select * from orderadvice_edit where orad_id=100' or > > 'select * from orderadvice_edit order by orad_name' both use one of these > > two partial indexes, given enough records are present in the table? > > > > There would be a double index on the primary key this way, right? > > It looks much better now. I'm not sure about the second index. Probably > it will be useless, because you sort ALL records with deleteddtata is > null. Maybe the first index will be enough. > > I'm not sure what to do with doubled index on a primary key field. > > Regards, > Tomasz Myrta > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend From tgl@sss.pgh.pa.us Fri Aug 29 14:00:27 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 E27ADD1B8B1 for ; Fri, 29 Aug 2003 14:00:27 +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 26389-03 for ; Fri, 29 Aug 2003 11:00:21 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id DEE02D1B8AC for ; Fri, 29 Aug 2003 11:00:15 -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 h7TE0EjX015993; Fri, 29 Aug 2003 10:00:14 -0400 (EDT) To: "Alexander Priem" Cc: "Tomasz Myrta" , pgsql-performance@postgresql.org Subject: Re: Indexing question In-reply-to: <01bf01c36e0d$18769280$b696a8c0@APR> References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> <018c01c36e09$4893e980$b696a8c0@APR> <3F4F1575.8090600@klaster.net> <01bf01c36e0d$18769280$b696a8c0@APR> Comments: In-reply-to "Alexander Priem" message dated "Fri, 29 Aug 2003 11:08:21 +0200" Date: Fri, 29 Aug 2003 10:00:14 -0400 Message-ID: <15992.1062165614@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/491 X-Sequence-Number: 3307 "Alexander Priem" writes: > Does anyone know whether it is bad practise to have two indexes on the > primary key of a table? (one 'primary key' index and one partial index) It's a little unusual, but if you get enough performance boost from it to justify the maintenance cost of the extra index, then I can't see anything wrong with it. The "if" is worth checking though. I missed the start of this thread, but what percentage of your rows do you expect to have null deleteddate? Unless it's a pretty small percentage, I'm unconvinced that the extra indexes will be worth their cost. regards, tom lane From ap@cict.nl Fri Aug 29 15:12:45 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 224D3D1B8AD for ; Fri, 29 Aug 2003 15:12:45 +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 29385-08 for ; Fri, 29 Aug 2003 12:12:13 -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 989EDD1B8A4 for ; Fri, 29 Aug 2003 12:12:12 -0300 (ADT) Received: from APR ([192.168.150.182]) by mail.cict.nl (Merak 6.0.5) with SMTP id DUC73886; Fri, 29 Aug 2003 17:12:00 +0200 Message-ID: <027701c36e40$2851aef0$b696a8c0@APR> From: "Alexander Priem" To: "Tom Lane" Cc: References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> <018c01c36e09$4893e980$b696a8c0@APR> <3F4F1575.8090600@klaster.net> <01bf01c36e0d$18769280$b696a8c0@APR> <15992.1062165614@sss.pgh.pa.us> Subject: Re: Indexing question Date: Fri, 29 Aug 2003 17:13:52 +0200 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit 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-Archive-Number: 200308/498 X-Sequence-Number: 3314 Well, the intention is to hold every record that ever existed in the table. Therefore, records do not get deleted, but they get a date in the deleteddate field. This way, we can track what changes were made to the table(s). So if a record gets 'deleted', the field 'deleted' is set to today's date. If a record gets 'updated', a new record is made containing the new data, and the old record is marked as 'deleted'. So the percentage of 'deleted' records will grow with time, if you understand what I mean. ----- Original Message ----- From: "Tom Lane" To: "Alexander Priem" Cc: "Tomasz Myrta" ; Sent: Friday, August 29, 2003 4:00 PM Subject: Re: [PERFORM] Indexing question > "Alexander Priem" writes: > > Does anyone know whether it is bad practise to have two indexes on the > > primary key of a table? (one 'primary key' index and one partial index) > > It's a little unusual, but if you get enough performance boost from it > to justify the maintenance cost of the extra index, then I can't see > anything wrong with it. > > The "if" is worth checking though. I missed the start of this thread, > but what percentage of your rows do you expect to have null deleteddate? > Unless it's a pretty small percentage, I'm unconvinced that the extra > indexes will be worth their cost. > > regards, tom lane From rellis9@yahoo.com Fri Aug 29 16:59:24 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 70A38D1B914 for ; Fri, 29 Aug 2003 16:59:24 +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 40703-05 for ; Fri, 29 Aug 2003 13:58:54 -0300 (ADT) Received: from dp100.dp100.com (dsl093-082-153.wdc1.dsl.speakeasy.net [66.93.82.153]) by svr1.postgresql.org (Postfix) with ESMTP id A00B3D1B8CC for ; Fri, 29 Aug 2003 13:58:51 -0300 (ADT) Received: from i386.dp100.com (i386.dp100.com [10.0.0.1]) by dp100.dp100.com (Postfix) with ESMTP id 5F3C12064 for ; Fri, 29 Aug 2003 12:58:51 -0400 (EDT) Received: by i386.dp100.com (Postfix, from userid 501) id 07877491F; Fri, 29 Aug 2003 12:58:51 -0400 (EDT) Date: Fri, 29 Aug 2003 12:58:51 -0400 From: Richard Ellis To: pgsql-performance@postgresql.org Subject: Re: Indexing question Message-ID: <20030829165850.GA28458@i386.dp100.com> Mail-Followup-To: pgsql-performance@postgresql.org References: <012f01c36dfa$0f4a8760$b696a8c0@APR> <3F4EFAB6.7030104@klaster.net> <015f01c36e02$134fe960$b696a8c0@APR> <3F4F07CE.6090803@klaster.net> <018c01c36e09$4893e980$b696a8c0@APR> <3F4F1575.8090600@klaster.net> <01bf01c36e0d$18769280$b696a8c0@APR> <15992.1062165614@sss.pgh.pa.us> <027701c36e40$2851aef0$b696a8c0@APR> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <027701c36e40$2851aef0$b696a8c0@APR> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/506 X-Sequence-Number: 3322 On Fri, Aug 29, 2003 at 05:13:52PM +0200, Alexander Priem wrote: > Well, the intention is to hold every record that ever existed in the table. > Therefore, records do not get deleted, but they get a date in the > deleteddate field. This way, we can track what changes were made to the > table(s). > > So if a record gets 'deleted', the field 'deleted' is set to today's date. > If a record gets 'updated', a new record is made containing the new data, > and the old record is marked as 'deleted'. > > So the percentage of 'deleted' records will grow with time, if you > understand what I mean. Did you consider a two table implimentation. 1 table "live_table" containing the non-deleted records, a second table "deleted_table" containing the deleted records, along with the "deleted_date" field. Keep the two in sync column type/number wise, and use a before delete trigger function on "live_table" to actually insert a copy of the deleted row plus "deleted_date" into "deleted_table" before performing the delete on "live_table". You could also use a before update trigger to keep old copies of updated records in the same way. Then you would only incur the performance loss of scanning/etc. the deleted records when you actually need to pull up deleted plus live records. From rudi@oasis.net.au Sat Aug 30 00:40:45 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 BB591D1B8B7 for ; Sat, 30 Aug 2003 00:40:45 +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 76762-01 for ; Fri, 29 Aug 2003 21:40:18 -0300 (ADT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by svr1.postgresql.org (Postfix) with ESMTP id AD4ABD1B8A4 for ; Fri, 29 Aug 2003 21:40:12 -0300 (ADT) Received: from oasis.net.au (localhost [127.0.0.1]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id LAA04651; Sat, 30 Aug 2003 11:07:43 +1000 X-Authentication-Warning: water.oasis.net.au: Host localhost [127.0.0.1] claimed to be oasis.net.au From: "Rudi Starcevic" To: "Alexander Priem" , Subject: Re: Indexing question Date: Sat, 30 Aug 2003 11:07:43 +1000 Message-Id: <20030830010743.M18111@oasis.net.au> In-Reply-To: <012f01c36dfa$0f4a8760$b696a8c0@APR> References: <012f01c36dfa$0f4a8760$b696a8c0@APR> X-Mailer: Open WebMail X-OriginatingIP: 203.221.163.92 MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/514 X-Sequence-Number: 3330 Hi, > I have some tables (which can get pretty large) in which I want to > record 'current' data as well as 'historical' data. Another solution can be using a trigger and function to record every transaction to a 'logging' table. This way you'll have one 'current' table and one 'historical' table The 'historical' table will contain every transaction recorded from the current table. Regards Rudi. From bielski@ece.cmu.edu Tue Jun 15 21:30:47 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E4750D1CAF5 for ; Tue, 15 Jun 2004 18:30:47 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 25468-05 for ; Tue, 15 Jun 2004 21:30:44 +0000 (GMT) Received: from bache.ece.cmu.edu (BACHE.ECE.CMU.EDU [128.2.129.23]) by svr1.postgresql.org (Postfix) with ESMTP id 7EE66D1CA34 for ; Tue, 15 Jun 2004 18:30:05 -0300 (ADT) Received: from elysium.pdl.cmu.edu (ELYSIUM.PDL.CMU.EDU [128.2.134.97]) by bache.ece.cmu.edu (Postfix) with SMTP id C6B8D73F for ; Tue, 15 Jun 2004 17:29:59 -0400 (EDT) Date: Tue, 15 Jun 2004 17:29:59 -0400 (EDT) From: Stan Bielski X-Sender: bielski@elysium.pdl.cmu.edu To: pgsql-performance@postgresql.org Subject: Indexing question Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/207 X-Sequence-Number: 7265 Hello all, I have a rather large table (~20 GB) of network logs taken over the period of a month and stored under postgres 7.3. I'm trying to create an indexing scheme so that I can do a query with a "where time > foo and time < bar" and get the results without too much waiting. With this in mind, I created a new database ordering each log entry by the time values (seconds, and microseconds), and then created an index on the time value for seconds. I didn't use the clusterdb command, but I did do a "select * into foo_tbl order by time", which I understand to be the functional equivalent. The result looks something like this: Table "public.allflow_tv_mydoom" Column | Type | Modifiers ------------+---------+----------- tv_s | bigint | tv_us | bigint | src | inet | dst | inet | sport | integer | dport | integer | bytes_in | bigint | bytes_out | bigint | isn_in | bigint | isn_out | bigint | num_starts | integer | result | integer | flags | integer | age_s | bigint | age_us | bigint | ttl_left | bigint | end_s | bigint | end_us | bigint | Indexes: allflow_tv_mydoom_x btree (tv_s) with tv_s, of course, being my value in seconds. I followed this up with an ANALYZE so postrgres could absorb this structure into its logic. However, whenever I want to do my query, it *insists* on doing a sequential scan, even when I explicitly turn sequential scan off in postgres.conf... here's an example: standb=# explain select * from allflow_tv_mydoom where tv_s < 1074200099 and tv_s > 107506499; QUERY PLAN ----------------------------------------------------------------------------------- Seq Scan on allflow_tv_mydoom (cost=100000000.00..102303307.94 rows=1 width=132) Filter: ((tv_s < 1074200099) AND (tv_s > 107506499)) (2 rows) In this query I'm basically asking for a day's worth of data. It should be straightforward: it's all laid out in order and indexed, but it still insists on (what I believe to be) tearing through the entire DB and filtering out the other time values as it sees them. Even if I want just the data from a particular second, it does the same thing: standb=# explain select * from allflow_tv_mydoom where tv_s = 1074200099; QUERY PLAN ------------------------------------------------------------------------------------- Seq Scan on allflow_tv_mydoom (cost=100000000.00..102140682.45 rows=145 width=132) Filter: (tv_s = 1074200099) (2 rows) Naturally, this is incredibly frustrating because it takes forever, regardless of the query. The funny thing though is that I have the same data in another table where it is ordered and indexed by IP address, and the queries use the index and work the way I want them to. Example: standb=# explain select * from flow_ip_mydoom where src = '10.0.5.5'; QUERY PLAN --------------------------------------------------------------------------------------------- Index Scan using flow_ip_mydoom_x on flow_ip_mydoom (cost=0.00..333.41 rows=376 width=132) Index Cond: (src = '10.0.5.5'::inet) (2 rows) Can someone please explain to me what's going on and how to fix it? If there's an easier way to 'jump' to different time regions in the table without indexing all the different seconds values, I'd like to know this as well. Thanks a bunch, -S From tgl@sss.pgh.pa.us Sun Jun 20 18:45:24 2004 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CACF2D1B220 for ; Sun, 20 Jun 2004 15:45:24 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13592-03 for ; Sun, 20 Jun 2004 18:45:21 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 6CE2BD1B1D0 for ; Sun, 20 Jun 2004 15:45:19 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5KIj65Z004120; Sun, 20 Jun 2004 14:45:06 -0400 (EDT) To: Stan Bielski Cc: pgsql-performance@postgresql.org Subject: Re: Indexing question In-reply-to: References: Comments: In-reply-to Stan Bielski message dated "Tue, 15 Jun 2004 17:29:59 -0400" Date: Sun, 20 Jun 2004 14:45:06 -0400 Message-ID: <4119.1087757106@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/210 X-Sequence-Number: 7268 Stan Bielski writes: > Table "public.allflow_tv_mydoom" > Column | Type | Modifiers > ------------+---------+----------- > tv_s | bigint | ^^^^^^ > Indexes: allflow_tv_mydoom_x btree (tv_s) > standb=# explain select * from allflow_tv_mydoom where tv_s < 1074200099 > and tv_s > 107506499; > [ gives seqscan ] This is a FAQ :-(. Unadorned integer constants are taken to be int4 not int8 (unless they are too large for int4), and cross-data-type comparisons are not indexable in existing releases. So you have to explicitly cast the comparison values to int8: explain select * from allflow_tv_mydoom where tv_s < 1074200099::bigint and tv_s > 107506499::bigint; (or use the standard CAST syntax if you prefer). 7.5 will have a fix for this ancient annoyance. BTW, is there a reason to be using tv_s+tv_us and not just a single timestamptz column? regards, tom lane