X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A3E23D1B4A2 for ; Mon, 26 Jul 2004 18:26:45 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30615-03 for ; Mon, 26 Jul 2004 21:26:38 +0000 (GMT) Received: from web13125.mail.yahoo.com (web13125.mail.yahoo.com [216.136.174.143]) by svr1.postgresql.org (Postfix) with SMTP id 80EC1D1B495 for ; Mon, 26 Jul 2004 18:26:34 -0300 (ADT) Message-ID: <20040726212636.5563.qmail@web13125.mail.yahoo.com> Received: from [63.78.248.48] by web13125.mail.yahoo.com via HTTP; Mon, 26 Jul 2004 14:26:36 PDT Date: Mon, 26 Jul 2004 14:26:36 -0700 (PDT) From: Litao Wu Subject: Re: Timestamp-based indexing To: Tom Lane , "Matthew T. O'Connor" Cc: "Harmon S. Nine" , pgsql-performance@postgresql.org In-Reply-To: <28247.1090857551@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/184 X-Sequence-Number: 7575 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 wrote: > "Matthew T. O'Connor" 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