Re: create index on a field of udt

Hi,
I find a way to create index, I create a function returns the 'id' field of
udt info, then I create index based on this function.
e.g

create type info as (id int, name text);

creat table test (id int, i info);

create or replace function getID(i info) returns int as
$$ select $1.id $$
language sql;

create index infoindex on test (getID(i));

I want to use this index, but after I insert lots of data to the table
'test' and run 'select * from test where i.id=5', it still use 'seqscan',
not 'index scan'. How can I verify the index is build correctly?

e.g.
insert into test values (generate_series(1, 3000000), (1, 'hi')::info);
explain select * from test where i.id=1;
the result is : seqscan

On Mon, Jun 29, 2015 at 1:57 PM, Charles Clavadetscher <
clavadetscher(at)swisspug(dot)org> wrote:

> Hello
>
>
>
> I am not sure it is that simple. Probably you need to create operator
> classes to be used for indexing.
>
>
>
> http://www.postgresql.org/docs/9.4/static/xtypes.html
>
>
>
> You are probably better off using the basic data type in your table and
> using a composite index.
>
>
>
> Bye
>
> Charles
>
>
>
>
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *John R Pierce
> *Sent:* Montag, 29. Juni 2015 07:51
> *To:* pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] create index on a field of udt
>
>
>
> On 6/28/2015 10:31 PM, Shujie Shang wrote:
>
> Oh, I didn't explain my question well, actually I want to create an index
> on an udt in a table.
>
>
>
> e.g.
>
> create type info as (id int, name text);
>
> creat table test (i info);
>
> I want to run:
>
> create index myindex on test (i.id)
>
>
> create table test of info primary key(id);
>
> or, if you want to use your type plus other stuff in the table, I believe
> its something like...
>
> create table test (i info, stuff...) primary key (i.id)
> or
> create index test(i.id);
>
>
> watch out for ambiguity if the type names match the table or field name.
> see http://www.postgresql.org/docs/current/static/rowtypes.html#AEN7836
>
>
> --
>
> john r pierce, recycling bits in santa cruz
>
>