pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedindexes using datetime
3+ messages / 3 participants
[nested] [flat]
* indexes using datetime
@ 1999-08-27 22:10 Michael Richards <miker@scifair.acadiau.ca>
0 siblings, 2 replies; 3+ messages in thread
From: Michael Richards @ 1999-08-27 22:10 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] indexes using datetime
@ 1999-08-29 00:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Michael Richards <miker@scifair.acadiau.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 1999-08-29 00:46 UTC (permalink / raw)
To: Michael Richards <miker@scifair.acadiau.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] indexes using datetime
@ 1999-09-06 15:25 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Michael Richards <miker@scifair.acadiau.ca>
1 sibling, 0 replies; 3+ messages in thread
From: Herouth Maoz @ 1999-09-06 15:25 UTC (permalink / raw)
To: Michael Richards <miker@scifair.acadiau.ca>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~1999-09-06 15:25 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-08-27 22:10 indexes using datetime Michael Richards <miker@scifair.acadiau.ca>
1999-08-29 00:46 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-06 15:25 ` Herouth Maoz <herouth@oumail.openu.ac.il>
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