Re: Timestamp-based indexing - Mailing list pgsql-performance
From | Litao Wu |
---|---|
Subject | Re: Timestamp-based indexing |
Date | |
Msg-id | 20040726212636.5563.qmail@web13125.mail.yahoo.com Whole thread Raw |
In response to | Re: Timestamp-based indexing (Tom Lane <tgl@sss.pgh.pa.us>) |
Responses |
Re: Timestamp-based indexing
|
List | pgsql-performance |
Hi, How about changing: CURRENT_TIMESTAMP - INTERVAL '10 minutes' to 'now'::timestamptz - INTERVAL '10 minutes' It seems to me that Postgres will treat it as a constant. Thanks, --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > "Matthew T. O'Connor" <matthew@zeut.net> writes: > > VACUUM FULL ANALYZE every 3 hours seems a little > severe. > > If rows are only deleted once a day, that's a > complete waste of time, > indeed. > > I'd suggest running a plain VACUUM just after the > deletion pass is done. > ANALYZEs are a different matter and possibly need to > be done every > few hours, seeing that your maximum timestamp value > is constantly > changing. > > >> monitor=# set enable_seqscan = false; > >> SET > >> monitor=# explain analyze select * from > "eventtable" where timestamp > > >> CURRENT_TIMESTAMP - INTERVAL '10 minutes'; > >> QUERY PLAN > >> > ------------------------------------------------------------------------------------------------------------------------------------- > > >> > >> Seq Scan on "eventtable" > (cost=100000000.00..100019009.97 rows=136444 > >> width=155) (actual time=9909.847..9932.438 > rows=1763 loops=1) > >> Filter: (("timestamp")::timestamp with time zone > > > >> (('now'::text)::timestamp(6) with time zone - '@ > 10 mins'::interval)) > >> Total runtime: 9934.353 ms > > You've got some datatype confusion, too. > CURRENT_TIMESTAMP yields > timestamp with time zone, and since you made the > timestamp column > timestamp without time zone, you've got a cross-type > comparison which is > not indexable (at least not in 7.4). My opinion is > that you chose the > wrong type for the column. Values that represent > specific real-world > instants should always be timestamp with time zone, > so that they mean > the same thing if you look at them in a different > time zone. > > Another issue here is that because CURRENT_TIMESTAMP > - INTERVAL '10 > minutes' isn't a constant, the planner isn't able to > make use of the > statistics gathered by ANALYZE anyway. That's why > the rowcount estimate > has nothing to do with reality. Unless you force > the decision with > "set enable_seqscan", the planner will never pick an > indexscan with this > rowcount estimate. The standard advice for getting > around this is to > hide the nonconstant calculation inside a function > that's deliberately > mislabeled immutable. For example, > > create function ago(interval) returns timestamp with > time zone as > 'select now() - $1' language sql strict immutable; > > select * from "eventtable" where timestamp > ago('10 > minutes'); > > The planner folds the "ago('10 minutes')" to a > constant, checks the > statistics, and should do the right thing. Note > however that this > technique may break if you put a call to ago() > inside a function > or prepared statement --- it's only safe in > interactive queries, > where you don't care that the value is reduced to a > constant during > planning instead of during execution. > > regards, tom lane > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose > an index scan if your > joining column's datatypes do not match > __________________________________ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail
pgsql-performance by date: