pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
indexing a datetime by date
9+ messages / 5 participants
[nested] [flat]

* indexing a datetime by date
@ 1999-03-30 03:27 Andrew Merrill <andrew@compclass.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Andrew Merrill @ 1999-03-30 03:27 UTC (permalink / raw)
  To: pgsql-sql@hub.org

I have a table with a field, "when", of type "datetime".  I can't use
"date" because I need the times as well.  I'm using PostgreSQL 6.4.2.

I'd like to identify all of the records with today's date, as in:

select when from notes where when::date = now()::date;

The query works, but is very slow.  Explain confirms that a sequential
scan is being used.

I've tried indexing on when:

create index when_ndx1 on notes (when);

But that doesn't help, as (I suppose) the optimizer can't match
when::date with this index.

Neither of these works:

db=> create index when_ndx2 on notes (when::date);
ERROR:  parser: parse error at or near "::"

db=> create index when_ndx3 on notes (date(when));
ERROR:  DefineIndex:  class not found

As a workaround, I've been using this:

select when from notes where when >= '3/29/1999 0:0:0' and when <=
'3/29/1999 23:59:59';

but that's ugly and requires hardcoding today's date each time, rather
than using now().

So, the question is, is there a way to index a datetime field by date?

Andrew Merrill




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: [SQL] indexing a datetime by date
@ 1999-03-30 04:07 Jackson, DeJuan <djackson@cpsgroup.com>
  1999-03-30 06:14 ` Re: [SQL] indexing a datetime by date Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-03-30 11:59 ` RE: [SQL] indexing a datetime by date Herouth Maoz <herouth@oumail.openu.ac.il>
  0 siblings, 2 replies; 9+ messages in thread

From: Jackson, DeJuan @ 1999-03-30 04:07 UTC (permalink / raw)
  To: Andrew Merrill <andrew@compclass.com>; pgsql-sql@hub.org; +Cc: Thomas G. Lockhart <lockhart@alumni.caltech.edu>; Tom Lane <tgl@sss.pgh.pa.us>; PGSQL Hackers <pgsql-hackers@hub.org>

Part of the problem is that PostgreSQL Assumes that a functions value will
change each time it is required, therefore automatic table scan and the
function is called for each row.
Try using 'now'::date instead of now()::date
You index creation syntax is good but there's a bug in function indexes
which require you to specify the ops. Try:
 create index when_ndx3 on notes (date(when) date_ops);

Which won't work because the date(datetime) function isn't trusted.
You can change this yourself in the system tables or you can use PL/PGSQL
(the only trustable PL in PostgreSQL that I've found) to create another
conversion function and use it instead.  Or you can as Thomas Lockhart (or
is it Tom Lane) if he'd create a trusted function for the conversions in
6.5.
DISCLAIMER: I haven't tested this on the current CSV(?CVS I just can't think
tonight) so it might already be fixed.
	-DEJ

> -----Original Message-----
> From: Andrew Merrill [mailto:andrew@compclass.com]
> Sent: Monday, March 29, 1999 9:28 PM
> To: pgsql-sql@hub.org
> Subject: [SQL] indexing a datetime by date
> 
> 
> I have a table with a field, "when", of type "datetime".  I can't use
> "date" because I need the times as well.  I'm using PostgreSQL 6.4.2.
> 
> I'd like to identify all of the records with today's date, as in:
> 
> select when from notes where when::date = now()::date;
> 
> The query works, but is very slow.  Explain confirms that a sequential
> scan is being used.
> 
> I've tried indexing on when:
> 
> create index when_ndx1 on notes (when);
> 
> But that doesn't help, as (I suppose) the optimizer can't match
> when::date with this index.
> 
> Neither of these works:
> 
> db=> create index when_ndx2 on notes (when::date);
> ERROR:  parser: parse error at or near "::"
> 
> db=> create index when_ndx3 on notes (date(when));
> ERROR:  DefineIndex:  class not found
> 
> As a workaround, I've been using this:
> 
> select when from notes where when >= '3/29/1999 0:0:0' and when <=
> '3/29/1999 23:59:59';
> 
> but that's ugly and requires hardcoding today's date each time, rather
> than using now().
> 
> So, the question is, is there a way to index a datetime field by date?
> 
> Andrew Merrill
> 
> 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] indexing a datetime by date
  1999-03-30 04:07 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
@ 1999-03-30 06:14 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-03-30 16:31   ` Re: [SQL] indexing a datetime by date Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 9+ messages in thread

From: Thomas Lockhart @ 1999-03-30 06:14 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Jackson, DeJuan <djackson@cpsgroup.com>; Andrew Merrill <andrew@compclass.com>; pgsql-sql@hub.org, PGSQL Hackers <pgsql-hackers@hub.org>

> Your index creation syntax is good but there's a bug in function 
> indexes which require you to specify the ops. Try:
>  create index when_ndx3 on notes (date(when) date_ops);
> Which won't work because the date(datetime) function isn't trusted.
> You can change this yourself in the system tables or you can use 
> PL/PGSQL (the only trustable PL in PostgreSQL that I've found) to 
> create another conversion function and use it instead.  Or you can as 
> Thomas Lockhart (or is it Tom Lane) if he'd create a trusted function 
> for the conversions in 6.5.

Tom, does this ring a bell with you? istm that (almost) all builtin
functions should be trusted, but I haven't done anything explicit
about it that I can remember.

In your new role as System Table Berserker, perhaps you would want to
fix this? :)

                       - Tom



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] indexing a datetime by date
  1999-03-30 04:07 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
  1999-03-30 06:14 ` Re: [SQL] indexing a datetime by date Thomas Lockhart <lockhart@alumni.caltech.edu>
@ 1999-03-30 16:31   ` Tom Lane <tgl@sss.pgh.pa.us>
  1999-03-30 17:25     ` Re: [SQL] indexing a datetime by date Andrew Merrill <andrew@compclass.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Tom Lane @ 1999-03-30 16:31 UTC (permalink / raw)
  To: Thomas Lockhart <lockhart@alumni.caltech.edu>; +Cc: Jackson, DeJuan <djackson@cpsgroup.com>; Andrew Merrill <andrew@compclass.com>; pgsql-sql@hub.org, PGSQL Hackers <pgsql-hackers@hub.org>

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:
>> create index when_ndx3 on notes (date(when) date_ops);
>> Which won't work because the date(datetime) function isn't trusted.

> Tom, does this ring a bell with you?

No, and in fact datetime_date *is* marked trusted in pg_proc,
both current sources and 6.4.2.

I see the problem DeJuan is getting at:

play=> create table notes (when datetime);
CREATE
play=> create index when_ndx3 on notes (date(when) date_ops);
CREATE
play=> insert into notes values ('now');
ERROR:  internal error: untrusted function not supported.

This is either a bug or a very poorly worded error message.
I'll look into it.

In the meantime, a workaround is to call the function using its
builtin name:

play=> create table notes (when datetime);
CREATE
play=> create index when_ndx3 on notes (datetime_date(when) date_ops);
CREATE
play=> insert into notes values ('now');
INSERT 1086489 1

In 6.4.2, date() on a datetime is an SQL-language function that just
calls the builtin function datetime_date().  It would seem that 6.4.2
can't cope with an SQL-language function as an index generator.  This
might be a minor bug or it might be difficult to change; I dunno.

In 6.5, date() on a datetime is a true builtin, on par with
datetime_date(), so you'll be able to use either name interchangeably in
that release.  But we may still not be able to do anything with other
SQL-language functions as index generators.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] indexing a datetime by date
  1999-03-30 04:07 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
  1999-03-30 06:14 ` Re: [SQL] indexing a datetime by date Thomas Lockhart <lockhart@alumni.caltech.edu>
  1999-03-30 16:31   ` Re: [SQL] indexing a datetime by date Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-03-30 17:25     ` Andrew Merrill <andrew@compclass.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Andrew Merrill @ 1999-03-30 17:25 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-sql@hub.org, PGSQL Hackers <pgsql-hackers@hub.org>

Tom Lane wrote:

> In the meantime, a workaround is to call the function using its
> builtin name:
>
> play=> create table notes (when datetime);
> CREATE
> play=> create index when_ndx3 on notes (datetime_date(when) date_ops);
> CREATE

Thanks, that helps - I can now index a datetime field by date.But the index
doesn't appear to be used:

db=> create index ndx3 on notes (datetime_date(when) date_ops);
CREATE
db=> vacuum analyze notes;
VACUUM
db=> explain select when from notes where when::date = 'now'::date;
NOTICE:  QUERY PLAN:

Seq Scan on notes  (cost=4330.37 size=43839 width=8)

EXPLAIN

So it appears that the optimizer doesn't like this index.  (This is with
version 6.4.2.)
The table has about 90,000 rows, of which between 10 and 100 might match a
given date, so an index would really help.

Am I missing something simple here?  Thanks again for all your help.

Andrew Merrill




^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: [SQL] indexing a datetime by date
  1999-03-30 04:07 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
@ 1999-03-30 11:59 ` Herouth Maoz <herouth@oumail.openu.ac.il>
  1 sibling, 0 replies; 9+ messages in thread

From: Herouth Maoz @ 1999-03-30 11:59 UTC (permalink / raw)
  To: pgsql-sql

At 06:07 +0200 on 30/03/1999, Jackson, DeJuan wrote:


>
> Part of the problem is that PostgreSQL Assumes that a functions value will
> change each time it is required, therefore automatic table scan and the
> function is called for each row.
> Try using 'now'::date instead of now()::date

How about using the ANSI standard CURRENT_DATE instead of either? It's
already of type date. Or is it considered a function call?

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma





^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: [SQL] indexing a datetime by date
@ 1999-03-30 17:21 Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Jackson, DeJuan @ 1999-03-30 17:21 UTC (permalink / raw)
  To: Herouth Maoz <herouth@oumail.openu.ac.il>; pgsql-sql


> How about using the ANSI standard CURRENT_DATE instead of either? It's
> already of type date. Or is it considered a function call?
> 
> Herouth
Not sure how PostgreSQL implements that.
It would appear to be implemented just as now().
Try:
 select CURRENT_DATE::TIME, now()::TIME;

	-DEJ



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* RE: [SQL] indexing a datetime by date
@ 1999-03-30 17:33 Jackson, DeJuan <djackson@cpsgroup.com>
  1999-03-30 18:11 ` Re: [SQL] indexing a datetime by date Andrew Merrill <andrew@compclass.com>
  0 siblings, 1 reply; 9+ messages in thread

From: Jackson, DeJuan @ 1999-03-30 17:33 UTC (permalink / raw)
  To: Andrew Merrill <andrew@compclass.com>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Thomas Lockhart <lockhart@alumni.caltech.edu>; Jackson, DeJuan <djackson@cpsgroup.com>; pgsql-sql@hub.org, PGSQL Hackers <pgsql-hackers@hub.org>

try:
 explain select when from notes where datetime_date(when) = 'now'::date;
	-DEJ

> Tom Lane wrote:
> 
> > In the meantime, a workaround is to call the function using its
> > builtin name:
> >
> > play=> create table notes (when datetime);
> > CREATE
> > play=> create index when_ndx3 on notes (datetime_date(when) 
> date_ops);
> > CREATE
> 
> Thanks, that helps - I can now index a datetime field by 
> date.But the index
> doesn't appear to be used:
> 
> db=> create index ndx3 on notes (datetime_date(when) date_ops);
> CREATE
> db=> vacuum analyze notes;
> VACUUM
> db=> explain select when from notes where when::date = 'now'::date;
> NOTICE:  QUERY PLAN:
> 
> Seq Scan on notes  (cost=4330.37 size=43839 width=8)
> 
> EXPLAIN
> 
> So it appears that the optimizer doesn't like this index.  
> (This is with
> version 6.4.2.)
> The table has about 90,000 rows, of which between 10 and 100 
> might match a
> given date, so an index would really help.
> 
> Am I missing something simple here?  Thanks again for all your help.
> 
> Andrew Merrill
> 



^ permalink  raw  reply  [nested|flat] 9+ messages in thread

* Re: [SQL] indexing a datetime by date
  1999-03-30 17:33 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
@ 1999-03-30 18:11 ` Andrew Merrill <andrew@compclass.com>
  0 siblings, 0 replies; 9+ messages in thread

From: Andrew Merrill @ 1999-03-30 18:11 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Thomas Lockhart <lockhart@alumni.caltech.edu>; pgsql-sql@hub.org, PGSQL Hackers <pgsql-hackers@hub.org>

Jackson, DeJuan wrote:

> try:
>  explain select when from notes where datetime_date(when) = 'now'::date;
>         -DEJ

Aha.  That does the trick.  Thanks!

Andrew Merrill




^ permalink  raw  reply  [nested|flat] 9+ messages in thread


end of thread, other threads:[~1999-03-30 18:11 UTC | newest]

Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-30 03:27 indexing a datetime by date Andrew Merrill <andrew@compclass.com>
1999-03-30 04:07 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
1999-03-30 06:14 ` Thomas Lockhart <lockhart@alumni.caltech.edu>
1999-03-30 16:31   ` Tom Lane <tgl@sss.pgh.pa.us>
1999-03-30 17:25     ` Andrew Merrill <andrew@compclass.com>
1999-03-30 11:59 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-03-30 17:21 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
1999-03-30 17:33 RE: [SQL] indexing a datetime by date Jackson, DeJuan <djackson@cpsgroup.com>
1999-03-30 18:11 ` Andrew Merrill <andrew@compclass.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