1

I am using Postgres. The table looks something like this:

tran_id SERIAL PRIMARY KEY,
account_id integer NOT NULL CONSTRAINT fk_account_id REFERENCES base.account(account_id) ON DELETE CASCADE,
tran_type base.my_enum not null,
tran_note varchar(64) not null,
process_id integer not null,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP

I am interested in being able to quickly find only the rows where process_id is 0. That value means that the row is pending for some kind of processing, non-0 rows are basically old log that I don't want archived because the application also needs to find the number of transactions for a certain account. So it is convenient to have the processed and pending data in the same table. I am not, however, interested in being able to quickly find rows where that field is anything other than 0 and I don't want to use resources on indexing those other values.

This table, in one implementation, has close to 200K accounts, each account around 200 transactions on average so about 40M rows, give or take. At any given time, there are maybe a few dozen new transactions to process, i.e. WHERE process_id = 0. Once it is processed, the process_id is updated to something other than 0.

I will never be searching for process_id that is other than 0. So I would be interested in having an index that points to only rows where that value is 0 and not necessarily bother with other values so that the index is more compact and faster. Is this possible?

5
  • Yes, split the table into 2. The one with only a few dozens rows can be indexed as you like (or not at all as it will be so small) and the other can have no index except the primary key. Commented May 7, 2014 at 16:07
  • "non-0 rows are basically old log that I don't want archived because the application also needs to find the number of transactions for a certain account"
    – amphibient
    Commented May 7, 2014 at 16:10
  • so your solution is not really good for me because it involves splitting the table
    – amphibient
    Commented May 7, 2014 at 16:10
  • I wonder what is the problem with splitting the table. Commented May 7, 2014 at 16:15
  • the problem, @ypercube, is that unionizing data later on becomes more complicated and less clean/elegant
    – amphibient
    Commented May 7, 2014 at 16:15

1 Answer 1

3

You can create a partial index that includes all columns of the table:

CREATE UNIQUE INDEX not_processed
    ON transactions 
        (tran_id, tran_type, tran_note, created_at)
    WHERE process_id = 0 ;
2
  • this looks better. but why wouldn't i just include the primary key in the index (tran_id) rather than all the fields?
    – amphibient
    Commented May 7, 2014 at 16:20
  • 1
    That would work as well. Postgres is not (AFAIK) very good at optimizing queries to use only indexes but where (and when) there is such an optimization, it will benefit from reading only from a very small index than having to read data spread across several disk pages. Commented May 7, 2014 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.