version: 7.3.2
Ok, not really sure if this a bug per se, but its non-intuitive, and it
goes against the advice stated in the user guide (page 150, "...there is
no plan that can beat sequentially fetching 1 page...")
I have an application that performs many inserts in a second (its doing
real-time data collection from other hardware), in the process of these
inserts, it is sometimes necessary to consult the following with:
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.
So, it appears that there is some performance problem in the seq_scan
logic, or in caching (like, maybe its willing to cache an index, but it
always goes to the disk for a seq_scan? Even so, I would think the OS
would cache it.), or something really non-intuitive is happening that
should be documented (the present documentation implied that I should
*not* create that index, but doing so was a significant improvement).
p.s. You may be wondering why i'm not using serial or sequences. I need
this application to be database agnostic.
