From: | Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> |
---|---|
To: | Hervé Piedvache <herve(at)elma(dot)fr> |
Cc: | <pgsql-sql(at)postgresql(dot)org>, Jean-Christophe Boggio <cat(at)thefreecat(dot)org> |
Subject: | Re: How are working index with date ? |
Date: | 2001-11-22 17:01:22 |
Message-ID: | 20011122085047.S75349-100000@megazone23.bigpanda.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-22 17:29:02 | Re: INDEXng date_trunc ... |
Previous Message | Brent Verner | 2001-11-22 15:29:05 | Re: INDEXng date_trunc ... |