Skip site navigation (1) Skip section navigation (2)

Partial index question

From: Anton Nikiforov <anton(at)nikiforov(dot)ru>
To: pgsql-general(at)postgresql(dot)org
Subject: Partial index question
Date: 2004-04-29 07:34:43
Message-ID: 4090B013.6060002@nikiforov.ru (view raw or flat)
Thread:
Lists: pgsql-general
Dear All,
I have a question about using partial indexes.
Lets say i have a table containing data types (table_datatype) and the 
table containing data entrys (table_data).
While inserting into data entrys i have to number the entrys according 
to it's type, so i always have to do
select max(id) from table_data where data_type=X;
And then insert a new value into the table data with this type and index.
Looks like there is no way to use sequences in this case without using 
different tables that will make application not so clear.
But "my" way is not so clear also because i could get a collision while 
concurrent inserts, so i have to control insertion from the application 
and always check that it is unique.
So i'm planning to use partable indexes and hope they will help in 
performance improving (the table data will contain millions of records 
of each type so without indexing the performance will be not good and it 
is not clear form me that it will be faster using complex index)
I know that i can do
create indexe ...... where type=X;
But is there any way to create all types of indexes at a time of 
database creation without using triggers and creating indexes from it?
The matter is that data types are being added by the user, so i do not 
know the indexes that i should create now.
And what will be faster?
CREATE UNIQUE INDEX type_index ON table_data (type, id);
or
CREATE UNIQUE INDEX type_1_index ON table_data (id) WHERE type=1;
CREATE UNIQUE INDEX type_X_index ON table_data (id) WHERE type=X;

-- 
Thanks in advance,
Best regads,
Anton Nikiforov

Responses

pgsql-general by date

Next:From: Hervé PiedvacheDate: 2004-04-29 07:35:56
Subject: Re: Postgre and Web Request
Previous:From: Shridhar DaithankarDate: 2004-04-29 06:56:28
Subject: Re: Postgre and Web Request

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group