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 < clavadetsc...@swisspug.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-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] *On Behalf Of *John R Pierce > *Sent:* Montag, 29. Juni 2015 07:51 > *To:* pgsql-general@postgresql.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 > >
