On Sat, 31 May 2003, Dave E Martin XXIII wrote:
> select next_id from unique_ids where name=whatever for update;
> update unique_ids set next_id=next_id+1 where name=whatever;
> pass on value of old next_id to other code...
>
> where unique_ids is:
>
> create table unique_ids (
> name text not null,
> next_id bigint not null
> ) without oids;
>
> Currently this table has one row in it, where name is 15 unicode
> characters long. It would seem that there would be no need for an index
> on name. However, doing:
>
> create index unique_ids__name on unique_ids(name);
>
> resulted in literally an order-of-magnatude increase in the speed of the
> application. (it went from 10-20 seconds to handle approximately 30
> records, to 1/2-3/4 second, and this was the only change). Presumably I
> would have never discovered this had I remembered to declare name as a
> primary key, which would have created the index. Experimenting around,
> and doing a vacuum full without the index didn't make any difference (I
> suspected that perhaps seq_scan had to go through a bunch of "dead"
> records). For some reason, postgresql is significantly slower doing the
> sequential scan than the index (I checked with explain and it is using
> the index when its present) in spite of there only being one row.
It may be just be a question of plan choice, but we'd need to see explain
analyze output to really make a reasonable guess.
