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

So I just ran a test case for hash, btree, gin_btree and brin indexes. Also
without indexes, and without primary keys.
* Testing "deliverynotes" table.
- Definition and use case:
It is a table contaning real delivery note headers of several years
It consists of 300k rows, 128 columns, 63 indexes, 243Mb of data
excluding indexes. Since is a table visible for users, almost every
column can be searched so we need lots of indexes. We do not need
searches to be the fastest possible, we only need to accelerate a
bit our user searches; without harming too much writes.
- Things to test:
- measure index creation times.
- measure index space.
- with indexes but without primary key
- with everything
- Create fully, delete everything and Insert again data in blocks
- Test updates for recent data

I attached the logs for every test, if anyone wants to see what i'm exactly
testing.
This was tested on my i5 laptop with 4Gb of RAM and a 120Gb SSD (OCZ
Agility 3). I'm trying to measure CPU time, not I/O time, so some
configurations and tests are specific to avoid as much as IO as I can.
I'm using a dev build for Postgresql 9.5 downloaded from git sources.

Conclusions:
- Gin_btree seems slower in almost every case. It's writes are marginally
better than regular btrees even when using work_mem=160MB. (May be 20%
faster than btree). They are smaller than I thought.
- BRIN indexes seem very fast for writes. For selects maybe is a blend
between having indexes and don't having them. They don't recognize that
some values are simply out of range of indexed values, and that's a pity.
If the values we want are packed together I guess I would get even better
results.
- Primary keys and uniqueness checks doesn't seem to make any difference
here.
- Having no indexes at all is faster than I imagined. (Sometimes it beats
BRIN or Btree) Maybe because the IO here is faster than usual.
- Hash indexes: i tried to do something, but they take too much time to
build and i don't know why. If creates are slow, updates should be slow
too. I'm not going to test them again.

And finally, don't know why but i couldn't vacuum or analyze tables. It
always get stalled without doing anything; so i had to comment every
vacuum. Maybe there is a bug in this dev version or i misconfigured
something.

El vie., 12 jun. 2015 a las 7:27, Simon Riggs (<simon(at)2ndquadrant(dot)com>)
escribió:

> On 5 June 2015 at 18:07, deavid <deavidsedice(at)gmail(dot)com> wrote:
>
>> There are several use cases where I see useful an index, but adding it
>> will slow too much inserts and updates.
>> For example, when we have 10 million rows on a table, and it's a table
>> which has frequent updates, we need several index to speed up selects, but
>> then we'll slow down updates a lot, specially when we have 10 or more
>> indexes.
>> Other cases involve indexes for text search, which are used only for user
>> search and aren't that important, so we want to have them, but we don't
>> want the overload they put whenever we write on the table.
>> I know different approaches that already solve some of those problems in
>> some ways (table partitioning, partial indexes, etc), but i don't feel they
>> are the solution to every problem of this kind.
>>
>> Some people already asked for "delayed write" indexes, but the idea gets
>> discarded because the index could get out of sync, so it can omit results
>> and this is unacceptable. But i think maybe that could be fixed in several
>> ways and we can have a fast and reliable index (but maybe not so fast on
>> selects).
>>
>
> This is exactly the use case and mechanism for BRIN indexes.
>
> --
> Simon Riggs http://www.2ndQuadrant.com/
> <http://www.2ndquadrant.com/>
>
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>