Обсуждение: How are working index with date ?

Hi,

I have some questions about date format and index ..

I have a simple table with 1 million records, only insert about 1000
insert by day ...

The Struct of this table is :
create table mylog (part text, id text, datelog timestamp);

I created two index for my test :
create index ix_datelog on mylog (datelog);
or
create index ix_datelog2 on mylog (date(datelog));

If I do a simple :
select * from mylog order by datelog desc limit 5;

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 mylogwhere 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 mylogwhere extract(month from datelog)=extract(month from timestamp 'now')  and
extract(yearfrom datelog)=extract(year from timestamp 'now')group by part;
 

select part, count(id) from mylogwhere 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.

So I have to declare the month and the year in constant mode like that :

select part, count(id) from mylog where datelog between '2001-11-01' and '2001-11-30' group by part;

but I have to find the last day of the month ... or the first day of the
next month to do :

select part, count(id) from mylog where datelog>='2001-11-01' and datelog<'2001-12-01' group by part;

Like that I use my index ...

BUT ... yes again ;)

For this case ... I don use my index ...

select part, count(id) from mylogwhere date(datelog)=date('now')-1group 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 !!

Thanks for your reply !

Sorry for my poor english ... ;)

regards,
-- 
Hervé

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