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

Re: Partial index question

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Anton Nikiforov <anton(at)nikiforov(dot)ru>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Partial index question
Date: 2004-04-29 09:56:15
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
On Thu, Apr 29, 2004 at 11:34:43 +0400,
  Anton Nikiforov <anton(at)nikiforov(dot)ru> wrote:
> 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.

Not if you have to really number by each data_type entry without gaps.
You could use one sequence for the whole table and calculate the numbers
when you select the data. Depending on how often you need to do that, that
might have acceptable performance and you could use a simpler scheme for
storing the data.

> 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.

You can avoid this by locking the table. If you do lots of inserts, updates
and/or deletes the contention caused by doing this may be a problem. If
it won't be, than simpler is probably better.

> 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;

You almost certainly don't want to use partial indexes to solve this
problem. You want to use a two column index on (data_type, id).

> 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?

Solving this problem is one reason you want to use a two column index.

In response to

pgsql-general by date

Next:From: Bruno Wolff IIIDate: 2004-04-29 09:59:42
Subject: Re: Partial index question
Previous:From: John Sidney-WoollettDate: 2004-04-29 08:53:42
Subject: Re: Partial index question

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