From: | Hervé Piedvache <herve(at)elma(dot)fr> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | Jean-Christophe Boggio <cat(at)thefreecat(dot)org> |
Subject: | How are working index with date ? |
Date: | 2001-11-22 11:57:33 |
Message-ID: | 3BFCE82D.5559BCF8@elma.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 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.
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 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 !!
Thanks for your reply !
Sorry for my poor english ... ;)
regards,
--
Hervé
From | Date | Subject | |
---|---|---|---|
Next Message | Koen Antonissen | 2001-11-22 13:30:15 | "posttime" time DEFAULT now() |
Previous Message | Gurudutt | 2001-11-22 10:40:18 | Re: Joins~ |