From: | Chris Cheston <ccheston(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | How to create an index for type timestamp column using rtree? |
Date: | 2004-07-13 05:51:27 |
Message-ID: | e071108e040712225174cda991@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Hi all,
I'm storing some timestamps as integers (UTF) in a table and I want to
query by <= and >= for times between a certain period. The table has
gotten quite large and is now very slow in querying.
I think it's time to create an index for the timestamp column.
I tried using an rtree (for <= and >= optimization):
create INDEX logs_timestamp ON logs using rtree (timestamp);
but I get
ERROR: data type integer has no default operator class for access
method "rtree"
You must specify an operator class for the index or define a
default operator class for the data type
Do I have to create an rtree type for my timestamp integer column?
Existing rtree columns are below.
Pls help.
Thanks,
Chris
server=> select am.amname as acc_method, opc.opcname as ops_name from
pg_am am, pg_opclass opc where opc.opcamid = am.oid order by
acc_method, ops_name;
acc_method | ops_name
------------+-----------------
btree | abstime_ops
btree | bit_ops
btree | bool_ops
btree | bpchar_ops
btree | bytea_ops
btree | char_ops
btree | cidr_ops
btree | date_ops
btree | float4_ops
btree | float8_ops
btree | inet_ops
btree | int2_ops
btree | int4_ops
btree | int8_ops
btree | interval_ops
btree | macaddr_ops
btree | name_ops
btree | numeric_ops
btree | oid_ops
btree | oidvector_ops
btree | text_ops
btree | time_ops
btree | timestamp_ops
btree | timestamptz_ops
btree | timetz_ops
btree | varbit_ops
btree | varchar_ops
hash | bpchar_ops
hash | char_ops
hash | cidr_ops
hash | date_ops
hash | float4_ops
hash | float8_ops
hash | inet_ops
hash | int2_ops
hash | int4_ops
hash | int8_ops
hash | interval_ops
hash | macaddr_ops
hash | name_ops
hash | oid_ops
hash | oidvector_ops
hash | text_ops
hash | time_ops
hash | timestamp_ops
hash | timestamptz_ops
hash | timetz_ops
hash | varchar_ops
rtree | bigbox_ops
rtree | box_ops
rtree | poly_ops
(51 rows)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2004-07-13 06:14:57 | Re: How to create an index for type timestamp column using rtree? |
Previous Message | Rod Taylor | 2004-07-13 02:06:42 | Re: query plan wierdness? |