Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.10.1/8.10.1) with ESMTP id e6LFF7h31061 for ; Fri, 21 Jul 2000 11:15:08 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA13357; Fri, 21 Jul 2000 11:15:05 -0400 (EDT) To: "Mitch Vincent" cc: pgsql-sql@postgresql.org Subject: Re: Timestamp indexes In-reply-to: <001d01bff323$39cdb250$4100000a@doot> References: <001d01bff323$39cdb250$4100000a@doot> Comments: In-reply-to "Mitch Vincent" message dated "Fri, 21 Jul 2000 10:52:00 -0400" Date: Fri, 21 Jul 2000 11:15:04 -0400 Message-ID: <13354.964192504@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200007/209 "Mitch Vincent" writes: > A while back I as told (by Tom Lane I *think*) that timestamp (previously > datetime) fields couldn't be indexed as such That's certainly not true now, if it ever was... regression=# create table applicants(resubmitted timestamp); CREATE regression=# create index applicants_i on applicants(resubmitted); CREATE regression=# explain select * from applicants where resubmitted = 'today'; NOTICE: QUERY PLAN: Index Scan using applicants_i on applicants (cost=0.00..8.14 rows=10 width=8) EXPLAIN > and that I should index them > using this method : > CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date > ("resubmitted") "date_ops" ); > Since almost all the queries that search that field search it casting the > field to date, I thought that would be OK.. It was for a while (in the 6.5.X > days) but it seems that 7.0.2 is treating this different. I can't get an > index scan on that field no matter what I do. Dunno, it works for me ... regression=# CREATE INDEX "applicants_resubmitted" on "applicants" regression-# (date("resubmitted") "date_ops" ); CREATE regression=# explain select * from applicants where date(resubmitted) = 'today'; NOTICE: QUERY PLAN: Index Scan using applicants_resubmitted on applicants (cost=0.00..8.16 rows=10 width=8) EXPLAIN regression=# explain select * from applicants where resubmitted::date = 'today'; NOTICE: QUERY PLAN: Index Scan using applicants_resubmitted on applicants (cost=0.00..8.16 rows=10 width=8) EXPLAIN You would want an index on date() of the field if this is what most of your queries look like --- a straight index on the timestamp isn't useful for such a query. But I don't know why you're not getting index scans. More details please? regards, tom lane