create index on a field of udt

On 6/28/2015 10:08 PM, Shujie Shang wrote:

create type info as (id int, name text);
I want to create index on info.id <http://info.id&gt;.

you can't create an index on a type, just on a table.

create table info (id serial primary key, name text);

or

create table info (id serial, name text);
alter table info add primary key(id);

or more generically,

create index on some_table ( some_field[,...] ) ;

(a primary key is a unique not null constraint, this implies an index in
postgresql)

--
john r pierce, recycling bits in santa cruz

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)

On Mon, Jun 29, 2015 at 1:23 PM, John R Pierce <pierce@hogranch.com> wrote:

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 <http://i.id&gt;)

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

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@postgresql.org [mailto:pgsql-general-owner@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 <http://i.id&gt; )

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

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@swisspug.org> wrote:

On Sun, Jun 28, 2015 at 10:31 PM, Shujie Shang <sshang@pivotal.io> 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)

It is a matter of finding the correct level of magic parentheses.

create index on test (((i).id));

The outer layer are always needed for creating indexes. The middle layer
are needed because you are indexing an expression, not a column. And the
inner layer is needed because, well, that is just how udt works.

Cheers,

Jeff

On 6/28/2015 11:24 PM, Shujie Shang wrote:

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

does not every row of that match i.id = 1 ?

try ...

insert into test values (generate_series(1, 3000000),
(generate_series(1, 3000000), 'hi')::info);
analyze test;
explain select * from test where getID(i) <http://i.id&gt;= 1;

--
john r pierce, recycling bits in santa cruz

+1

create index on test (((i).id));

ANALYZE

explain select * from test where (i).id = 8909;

QUERY PLAN

-------------------------------------------------------------------------

Index Scan using test_id_idx on test (cost=0.43..8.45 rows=1 width=34)

Index Cond: ((i).id = 8909)

(2 rows)

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Jeff Janes
Sent: Montag, 29. Juni 2015 08:42
To: Shujie Shang
Cc: John R Pierce; PostgreSQL mailing lists
Subject: Re: [GENERAL] create index on a field of udt

On Sun, Jun 28, 2015 at 10:31 PM, Shujie Shang <sshang@pivotal.io <mailto:sshang@pivotal.io> > 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 <http://i.id&gt; )

It is a matter of finding the correct level of magic parentheses.

create index on test (((i).id));

The outer layer are always needed for creating indexes. The middle layer are needed because you are indexing an expression, not a column. And the inner layer is needed because, well, that is just how udt works.

Cheers,

Jeff