pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Chris Cheston <ccheston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: How to create an index for type timestamp column using rtree?
Date: Mon, 12 Jul 2004 22:51:27 -0700
Message-ID: <e071108e040712225174cda991@mail.gmail.com> (raw)

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)



view thread (4+ messages)  latest in thread

Message-ID: <e071108e040712225174cda991@mail.gmail.com>
Permalink:  ../e071108e040712225174cda991@mail.gmail.com/
Also on:    postgresql.org/message-id/e071108e040712225174cda991@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: ccheston@gmail.com
  Subject: Re: How to create an index for type timestamp column using rtree?
  In-Reply-To: <e071108e040712225174cda991@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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