Re: PATCH: index-only scans with partial indexes

I have applied this patch to our working branch and  during several 
weeks we ran various tests and benchmarks.
We have not noticed any problems or performance degradation.
And at some queries this patch cause very significant increase of 
performance - ten times:

With this patch:

postgres=# explain analyze select count(*) from t where k1<1000000 and 
pk < 1454434742881892;                                                       QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=29.65..29.66 rows=1 width=0) (actual 
 
time=0.108..0.108 rows=1 loops=1)   ->  Index Only Scan using idx1 on t  (cost=0.43..27.49 rows=861 
width=0) (actual time=0.012..0.071 rows=963 loops=1)         Index Cond: (k1 < 1000000)         Heap Fetches: 0
Planningtime: 0.100 ms Execution time: 0.121 ms
 
(6 rows)


Without patch:

postgres=# explain analyze select count(*) from t where k1<1000000 and 
pk < 1454434742881892;                                                       QUERY PLAN

------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2951.55..2951.56 rows=1 width=0) (actual 
 
time=1.070..1.070 rows=1 loops=1)   ->  Bitmap Heap Scan on t  (cost=19.10..2949.40 rows=861 width=0) 
(actual time=0.161..0.997 rows=963 loops=1)         Recheck Cond: ((k1 < 1000000) AND (pk < 
'1454434742881892'::bigint))         Heap Blocks: exact=954         ->  Bitmap Index Scan on idx1  (cost=0.00..18.88
rows=861
 
width=0) (actual time=0.083..0.083 rows=963 loops=1)               Index Cond: (k1 < 1000000) Planning time: 0.099 ms
Executiontime: 1.089 ms
 
(8 rows)




On 01.02.2016 01:11, Alvaro Herrera wrote:
> Konstantin Knizhnik wrote:
>> I am very interested in this patch because it allows to use partial indexes to ... speed up inserts.
>> I have implemented "ALTER INDEX ... WHERE ..." construction which allows to change predicate of partial index
withoutnecessity to fully rebuild it.
 
>> So it is not necessary to insert new records in index immediately (if new records do not match partial index
conditions).
>> It can be done later in background (or at night). My experiments show that it allows to increase insert speed five
times(for either partial indexes).
 
>> At the same time we do not loose RDBMS requirement that result of query should not depend on presence of indexes.
Andit is applicable to all indexes: B-Tree, GIN, GIST,...
 
>>
>> But such optimization makes sense only of partial indexes can be used without extra overhead, first of all for
index-onlyscans.
 
>> And it is impossible without this patch.
> That sounds interesting.  So please review this patch and let us know
> whether you like it, or whether you have any better ideas for any
> particular hunk, or whether you think it should be rewritten from
> scratch, or just state that it is perfect.  If you think it's useful,
> then it's a good idea to vouch for it to be integrated; and one way of
> doing that is making sure it meets the quality standards etc.  If you
> don't say anything about the patch, we may never integrate it because we
> might have doubts about whether it's worthy.
>

-- 
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company