From: Ace <gracec@ntsp.nec.co.jp>
To: Greg Stark <gsstark@mit.edu>
Cc: PGSQL_SQL <pgsql-sql@postgresql.org>
Subject: Re: Index not recognized
Date: Tue, 9 Dec 2003 16:37:24 +0800
Message-ID: <056801c3be2f$ab4e6d90$ec64a8c0@GREECE> (raw)
References: <3FD0A87B.4010803@scmbb.ulb.ac.be>
<001101c3bbb6$8b0db840$ec64a8c0@GREECE>
<87ptf11n1j.fsf@stark.dyndns.tv>
Hello Greg, thanks for replying.
I definitely lacked the expression you suggested in my 'SELECT'
statement.
> Is this *= operator from the contrib/array directory? It's not an
indexable
> operator at all using standard btree indexes.
Yes, it is from contrib/array directory.
> The GiST indexing does make indexable operators that can do things like *=
but
> that's a whole other ball of wax.
I tried the btree_gist from contrib/, but I know I missed something because
I got
this error message:
data type text[] has no default operator class for access method "gist". You
must
specify an operator class for the index or define a default operator class
for the
data type.
> What are you really trying to do?
I have tables with attributes whose datatype is TEXT[].
I'm interested to find out the time it will take to finish an array search
with and
without an index.
---
Grace
----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
To: "Grace C. Unson" <gracec@ntsp.nec.co.jp>
Cc: "PgSQL SQL" <pgsql-sql@postgresql.org>
Sent: Sunday, December 07, 2003 8:36 AM
Subject: Re: [SQL] Index not recognized
>
> "Grace C. Unson" <gracec@ntsp.nec.co.jp> writes:
>
> > Why is it that my index for text[] data type is not recognized by the
> > Planner?
> >
> > I did these steps:
> >
> > 1. create function textarr(text[]) returns text language sql as 'select
> > $1[1]' strict immutable
> > 2. create index org_idx on EmpData (textarr(org));
>
> This index will only be used if you use the expression textarr(org) in
your
> query. You would probably have some success if you did:
>
> select * from empdata where textarr(org) = 'math'
>
> > 3. vacuum full
> > 4. explain analyze select name from EmpData where org *= 'math';
>
> Is this *= operator from the contrib/array directory? It's not an
indexable
> operator at all using standard btree indexes.
>
> The GiST indexing does make indexable operators that can do things like *=
but
> that's a whole other ball of wax.
>
> What are you really trying to do?
>
> > Result:
> > =========
> > Seq Scan on EmpData (cost=0.00..3193.20 rows=102 width=488)
> > (actual time=3.71.35..371.35 rows=0 loops=1)
> >
> > Filter: (org[0]='math'::text)
>
> Well that's awfully odd. I don't know how that expression came out of the
> query you gave. You'll have to give a lot more information about how
you're
> defining *= and why you think it's related to the function you used to
define
> the index.
>
> --
> greg
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
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-sql@postgresql.org
Cc: gracec@ntsp.nec.co.jp, gsstark@mit.edu
Subject: Re: Index not recognized
In-Reply-To: <056801c3be2f$ab4e6d90$ec64a8c0@GREECE>
* 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