Database Reference
In-Depth Information
02:23:28.470979")
(7 rows)
Time: 2.949 ms
Much better—indexed lookups are 300 times faster than sequential scans, and this
difference will grow as tables get bigger!
As we are updating the messages and setting their status to read, it is also a good
idea to set the fillfactor to something less than 100 percent.
Note
Fillfactor tells PostgreSQL not to fill up database pages completely but to leave
some space for HOT updates. When PostgreSQL updates a row, it only marks
the old row for deletion and adds a new row to the data file. If the row that is
updated only changes unindexed fields and there is enough room in the page to
store a second copy, a HOT update will be done instead. In this case, the copy
can be found using original index pointers to the first copy, and no expensive in-
dex updates are done while updating.
hannu=# ALTER TABLE message SET (fillfactor =
90);
ALTER TABLE
Time: 75.729 ms
hannu=# CLUSTER message_from_user_ndx ON
message;
CLUSTER
Time: 9797.639 ms
hannu=# select get_new_messages('55022');
get_new_messages
----------------------------------------------------------
(200,"hello friend!",55014,"2012-01-09
02:23:28.470979")
