Horton

Thread

  1. Index speeds up one row table (why)?

    Dave E Martin XXIII <postgresql-to.dave@dave.to> — 2003-05-31T06:14:18Z

    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.

  2. Re: Index speeds up one row table (why)?

    Bruno Wolff III <bruno@wolff.to> — 2003-05-31T13:24:03Z

    On Sat, May 31, 2003 at 00:14:18 -0600,
  Dave E Martin XXIII <postgresql-to.dave@dave.to> wrote:
> version: 7.3.2
> 
> 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:

It probably has one visible row in it. If it can changed a lot, there
may be lots of deleted tuples in a row. That would explain why an
index scan speeds things up.

  3. Re: Index speeds up one row table (why)?

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-31T15:19:20Z

    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.

  4. Re: Index speeds up one row table (why)?

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-05-31T15:43:32Z

    Bruno Wolff III <bruno@wolff.to> writes:
> It probably has one visible row in it. If it can changed a lot, there
> may be lots of deleted tuples in a row. That would explain why an
> index scan speeds things up.

Right, every UPDATE on unique_ids generates a dead row, and a seqscan
has no alternative but to wade through them all.  When a unique index is
present, the indexscan code knows that after it's fetched one live tuple
there can be no more matching the same index key, and so it need not
keep examining index entries.  Furthermore, due to the way that btree
handles equal keys, it is likely (not certain, just likely) that
more-recent and hence more-likely-to-be-live tuples will be seen first.

However, the above-described optimization for unique keys is new in
7.3.*, and it's buggy.  It's disabled as of 7.3.3, so the performance
improvement you're seeing will go away as soon as you update (which you
should).  There's a fresh try at it in 7.4 CVS.

More-frequent vacuums would be a much more reliable solution, in any
case.  If you are updating the single row once a second, then a cron job
to vacuum (not full, just plain "vacuum") that particular table every
couple of minutes would not be a bad idea.  A hundred dead rows will
still fit in one disk block (unless there's lots more in the row than
you've mentioned), and as long as you can keep the table to one disk
block you shouldn't notice any performance degradation.

You might care to use contrib/pgstattuple to check out the contents of
the table, but I'm pretty sure what you'll find ... 

			regards, tom lane

  5. Re: Index speeds up one row table (why)?

    Dave E Martin XXIII <postgresql-to.dave@dave.to> — 2003-05-31T23:17:38Z

    Tom Lane wrote:

>Bruno Wolff III <bruno@wolff.to> writes:
>  
>
>>It probably has one visible row in it. If it can changed a lot, there
>>may be lots of deleted tuples in a row. That would explain why an
>>index scan speeds things up.
>>    
>>
>
>Right, every UPDATE on unique_ids generates a dead row, and a seqscan
>has no alternative but to wade through them all.  When a unique index is
>
Speaking of which, since the row is locked with select for update (so it 
can't be involved in any other transactions anyway) and the change 
doesn't change the length of the row, can't it just be updated in-place, 
or would that break something else? (pardon if this is answered already, 
me thinks its time to go reread the todo's and the architecture 
documents...)

  6. Re: Index speeds up one row table (why)?

    Bruno Wolff III <bruno@wolff.to> — 2003-06-01T05:36:50Z

    On Sat, May 31, 2003 at 17:17:38 -0600,
  Dave E Martin XXIII <postgresql-to.dave@dave.to> wrote:
> Speaking of which, since the row is locked with select for update (so it 
> can't be involved in any other transactions anyway) and the change 
> doesn't change the length of the row, can't it just be updated in-place, 
> or would that break something else? (pardon if this is answered already, 
> me thinks its time to go reread the todo's and the architecture 
> documents...)

No. Select for update only blocks writers, not readers. This has important
performance advantages. You might want read the documentation on MVCC.
Tom Lane also has a copy of a presentation he made on the web somewhere.
I have it read it but it has gotten favorable mention on the lists before.