From: Greg Stark <gsstark@mit.edu>
To: Grace C. Unson <gracec@ntsp.nec.co.jp>
Cc: PgSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Index not recognized
Date: 06 Dec 2003 19:36:40 -0500
Message-ID: <87ptf11n1j.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <001101c3bbb6$8b0db840$ec64a8c0@GREECE>
References: <3FD0A87B.4010803@scmbb.ulb.ac.be>
<001101c3bbb6$8b0db840$ec64a8c0@GREECE>
"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
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: gsstark@mit.edu, gracec@ntsp.nec.co.jp
Subject: Re: Index not recognized
In-Reply-To: <87ptf11n1j.fsf@stark.dyndns.tv>
* 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