Thread: indexes using datetime

Hi.
I've got a slow query I can't seem to fix...
Table    = logins
+-------------------------+----------------------------------+-------+
|           Field         |              Type                | Length|
+-------------------------+----------------------------------+-------+
| loginid                 | varchar()                        |    16 |
| logintime               | datetime                         |     8 |
| ip                      | varchar()                        |    15 |
| direction               | char()                           |     1 |
+-------------------------+----------------------------------+-------+
Indices:  logins_direction_idx         logins_logintime_idx

So I've got this table that records stuff about people logging in. Since
it's essentially a log, it is very large. About 1.3 million tuples.

I've created an index on the logintime with hopes I can make some of my
queries which care only about the last 40 day's of logins use the query
and ignore the rest of the tuples...

explain select * from logins where logintime>'now'::datetime-'40
days'::timespan;
NOTICE:  QUERY PLAN:

Seq Scan on logins  (cost=5839.78 rows=44958 width=44)

Very bad query plan :(

As I recall, you should be able to make a btree traversal return all its
values in order. So then isn't it reasonable that the dbms should be using
the index to find all the values within a certain range?

-Michael

Michael Richards <miker@scifair.acadiau.ca> writes:
> explain select * from logins where logintime>'now'::datetime-'40
> days'::timespan;
> NOTICE:  QUERY PLAN:
> Seq Scan on logins  (cost=5839.78 rows=44958 width=44)

> Very bad query plan :(

Yah.  It's got nothing to do with datetime though.  Problem is that the
system can only make indexscans work with WHERE clauses of the form
"field op constant" (for "op"s related to the sort ordering of the
index of course).  Your righthand side is not a constant.

Eventually, maybe for 6.6 or 6.7, we will have the smarts in place to
reduce constant expressions to simple constants during preprocessing of
a query.  For now, you are going to have to structure your application to
precompute the value that's going to be compared against index entries.
        regards, tom lane

At 03:46 +0300 on 29/08/1999, Tom Lane wrote:


> Michael Richards <miker@scifair.acadiau.ca> writes:
> > explain select * from logins where logintime>'now'::datetime-'40
> > days'::timespan;
> > NOTICE:  QUERY PLAN:
> > Seq Scan on logins  (cost=5839.78 rows=44958 width=44)
>
> > Very bad query plan :(
>
> Yah.  It's got nothing to do with datetime though.  Problem is that the
> system can only make indexscans work with WHERE clauses of the form
> "field op constant" (for "op"s related to the sort ordering of the
> index of course).  Your righthand side is not a constant.

A tip: use

SELECT 'now'::datetime - '40 days'::timespan;

Get the result on your client side, say in a variable named $x, and use it
in the "real" query:

SELECT * FROM logins WHERE logintime > $x;

(Actual embedding protocol depends, of course, on the language you use).

Then it's a constant, as far as Postgres is concerned, and the time wasted
for the small query is really not an issue.

Herouth

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