Received: from megazone.bigpanda.com ([63.150.15.178]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAMH1Hm44373 for ; Thu, 22 Nov 2001 12:01:17 -0500 (EST) (envelope-from sszabo@megazone23.bigpanda.com) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 29F0ED5E5; Thu, 22 Nov 2001 09:01:22 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 1FA8E5BE8; Thu, 22 Nov 2001 09:01:22 -0800 (PST) Date: Thu, 22 Nov 2001 09:01:22 -0800 (PST) From: Stephan Szabo To: =?iso-8859-1?Q?Herv=E9?= Piedvache Cc: , Jean-Christophe Boggio Subject: Re: How are working index with date ? In-Reply-To: <3BFCE82D.5559BCF8@elma.fr> Message-ID: <20011122085047.S75349-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200111/323 X-Sequence-Number: 5705 > The answer is immediate ... OK I understand that postgresql do not need > to make an order after the extraction because the optimizer think that > it's quicker to read the data from the index then read all the data > sequencialy and making the order by in RAM after (so slow) ... > > But I tried several requests that never use my index : > > select part, count(id) > from mylog > where date_part('month',datelog)=date_part('month',timestamp 'now') > and date_part('year',datelog)=date_part('year',timestamp 'now') > group by part; > > select part, count(id) > from mylog > where extract(month from datelog)=extract(month from timestamp 'now') > and extract(year from datelog)=extract(year from timestamp 'now') > group by part; > > select part, count(id) > from mylog > where to_char(datelog,'YYYY/MM')=to_char(now(),'YYYY/MM') > group by part; > > I accept the way that the index is not used because I modify two times > the left sentence of the WHERE. I think if you make a function that does the year/month stuff and mark it cachable and make an index on it and a function that's cachable create function func1(date) returns text as 'select to_char($1, ''YYYY/MM'');' language 'sql' with (iscachable); create function func2() returns text as 'select to_char(now(), ''YYYY/MM'');' language 'sql' with (iscachable); create index mylog_funcind on mylog(func1(datelog)); select ... where func1(datelog)=func2() ... may use that index for you. You could do the same thing with function being like extract(year...)*12+extract(month) or whatever probably. > select part, count(id) > from mylog > where date(datelog)=date('now')-1 > group by part > > and also not like this : > > select part, count(id) > from mylog > where date(datelog)=date('yesterday') > group by part > > I win some cost in the explain ... but I never use any index ... > > So does this index is usefull or not ... ? > I make many statistics with date ... and I'm really disapointed !! Well, 'now' generally doesn't use indexes to begin with because it's not considered a constant. You can probably get around this by making a function that's marked iscachable that returns the date('now')-1 or date('yesterday'). You can get more info out of the archives.