pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow are working index with date ?
2+ messages / 2 participants
[nested] [flat]
* How are working index with date ?
@ 2001-11-22 11:57 Hervé Piedvache <herve@elma.fr>
2001-11-22 17:01 ` Re: How are working index with date ? Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Hervé Piedvache @ 2001-11-22 11:57 UTC (permalink / raw)
To: pgsql-sql; +Cc: Jean-Christophe Boggio <cat@thefreecat.org>
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é
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: How are working index with date ?
2001-11-22 11:57 How are working index with date ? Hervé Piedvache <herve@elma.fr>
@ 2001-11-22 17:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2001-11-22 17:01 UTC (permalink / raw)
To: Hervé Piedvache <herve@elma.fr>; +Cc: pgsql-sql; Jean-Christophe Boggio <cat@thefreecat.org>
> 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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-11-22 17:01 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-22 11:57 How are working index with date ? Hervé Piedvache <herve@elma.fr>
2001-11-22 17:01 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox