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
Planning time: 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
Execution time: 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 without necessity 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. And it 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-only scans.
>> 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