pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow 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>
2004-07-13 06:14 ` Re: How to create an index for type timestamp column using rtree? 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>
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 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>
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 05:51 How to create an index for type timestamp column using rtree? Chris Cheston <ccheston@gmail.com>
@ 2004-07-13 06:33 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-07-13 07:56 ` Re: How to create an index for type timestamp column using rtree? 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 05:51 How to create an index for type timestamp column using rtree? Chris Cheston <ccheston@gmail.com>
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>
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