Re: How are working index with date ?

> 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.