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

Sorry; Because some misconfiugration vacuum and analyze were'nt working.
Now I'm getting better numbers for BRIN indexes where there are zero rows
to match.

El sáb., 13 jun. 2015 a las 3:17, deavid (<deavidsedice(at)gmail(dot)com>)
escribió:

> 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
>>
>