Re: Partial index question

Anton Nikiforov said:
> 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;

Not really answering your question directly, but some alternative
stragegies are:

If the set of types is small, then using sequences would be manageable.
Create a function that returns the next value for the passed-in data_type.
If the sequence does not yet exist for that id, you create the sequence,
and then return the sequence value.

If the set is large, then consider using an extra table which creates a
mapping between the data_type, and the last allocated value. Again create
a function to increment and return the next id for that data type.

My feeling (and I may be wrong) is that SELECT MAX(id) FROM table_data
WHERE type=x is always going to be less efficient that one of the above
methods regardless of the types of index you use, especially as the table
gets larger.

John Sidney-Woollett