Search Postgresql Archives

Re: create index on a field of udt

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of John R Pierce
Sent: Montag, 29. Juni 2015 07:51
To: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux