pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How to create an index for type timestamp column using rtree?
4+ messages / 3 participants
[nested] [flat]

* How to create an index for type timestamp column using rtree?
@ 2004-07-13 05:51  Chris Cheston <ccheston@gmail.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Chris Cheston @ 2004-07-13 05:51 UTC (permalink / raw)
  To: 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)



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to create an index for type timestamp column using rtree?
@ 2004-07-13 06:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Cheston <ccheston@gmail.com>
  1 sibling, 0 replies; 4+ messages in thread

From: Tom Lane @ 2004-07-13 06:14 UTC (permalink / raw)
  To: Chris Cheston <ccheston@gmail.com>; +Cc: pgsql-performance

Chris Cheston <ccheston@gmail.com> writes:
> I'm storing some timestamps as integers (UTF) in a table and I want to
> query by <= and >= for times between a certain period.

btree can handle range queries nicely; why do you think you need an
rtree?  rtree is for 2-dimensional datums which a timestamp is not ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to create an index for type timestamp column using
@ 2004-07-13 06:33  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Chris Cheston <ccheston@gmail.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Christopher Kings-Lynne @ 2004-07-13 06:33 UTC (permalink / raw)
  To: Chris Cheston <ccheston@gmail.com>; +Cc: pgsql-performance

> 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.

Uh, yeah.

> I tried using an rtree (for <= and >= optimization):

Bad idea.

> Do I have to create an rtree type for my timestamp integer column?  

Why do you want an rtree index?  They're for multidimensional polygonal 
data and stuff.  Just create a normal index...

Chris




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: How to create an index for type timestamp column using rtree?
@ 2004-07-13 07:56  Chris Cheston <ccheston@gmail.com>
  parent: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 4+ messages in thread

From: Chris Cheston @ 2004-07-13 07:56 UTC (permalink / raw)
  To: pgsql-performance

Thanks,  Chris and Tom.
I had read *incorrectly* that rtrees are better for <= and >= comparisons.

Chris

On Tue, 13 Jul 2004 14:33:48 +0800, Christopher Kings-Lynne
<chriskl@familyhealth.com.au> wrote:
> > 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.
> 
> Uh, yeah.
> 
> > I tried using an rtree (for <= and >= optimization):
> 
> Bad idea.
> 
> > Do I have to create an rtree type for my timestamp integer column?
> 
> Why do you want an rtree index?  They're for multidimensional polygonal
> data and stuff.  Just create a normal index...
> 
> Chris
> 
>



^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-07-13 07:56 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-13 05:51 How to create an index for type timestamp column using rtree? Chris Cheston <ccheston@gmail.com>
2004-07-13 06:14 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-13 06:33 ` Re: How to create an index for type timestamp column using Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-13 07:56   ` Chris Cheston <ccheston@gmail.com>

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