Re: Is it possible to have a "fast-write" Index?

deavid wrote:
> I did another try on BRIN and GIN indexes, and I compared to regular btree
> indexes. Now i have 16M rows to do the test.
>
> The numbers seem to be good. Both GIN and BRIN seem good options for
> certain tables with more writes than reads (Specially BRIN is very good)

Thanks, that's very nice to hear.

So you may or may not have realized two important details regarding
BRIN. One is that when you insert into a table, the values are not
immediately put into the index but only as part of a later vacuum, or a
brin_summarize_new_values() function call. Either of those things scan
the not-already-summarized part of the table and insert the summarized
values into the index. If the new values are not in the index, then a
query would have to read that part of the table completely instead of
excluding it from the scan.

The other thing is that in BRIN you can tell the system to make the
summary information very detailed or coarsely detailed -- say one
summary tuple for every page, or one summary tuple for every 128 pages.
The latter is the default. Obviously, the more detailed it is, the more
you can skip when scanning the table. If the values are perfectly
correlated, then there's no point to the extra detail, but if there's
some variability then it could be worthwhile. You change this by
specifying "WITH (pages_per_range=16)" to the CREATE INDEX command, or
by doing ALTER INDEX SET (pages_per_range=16) and then REINDEX it.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services