pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hervé Piedvache <herve@elma.fr>
To: pgsql-sql@postgresql.org
Cc: Jean-Christophe Boggio <cat@thefreecat.org>
Subject: How are working index with date ?
Date: Thu, 22 Nov 2001 12:57:33 +0100
Message-ID: <3BFCE82D.5559BCF8@elma.fr> (raw)

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é



view thread (2+ messages)  latest in thread

Message-ID: <3BFCE82D.5559BCF8@elma.fr>
Permalink:  ../3BFCE82D.5559BCF8@elma.fr/
Also on:    postgresql.org/message-id/3BFCE82D.5559BCF8@elma.fr

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: herve@elma.fr, cat@thefreecat.org
  Subject: Re: How are working index with date ?
  In-Reply-To: <3BFCE82D.5559BCF8@elma.fr>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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