Re: WIP: Covering + unique indexes. (the good and the bad) - Mailing list pgsql-hackers

On 2018-04-06 20:08, Alexander Korotkov wrote:
> 
> [0001-Covering-v15.patch]
> 

After some more testing I notice there is also a down-side/slow-down to 
this patch that is not so bad but more than negligible, and I don't 
think it has been mentioned (but I may have missed something in this 
thread that's now been running for 1.5 year, not to mention the 
tangential btree-thread(s)).

I attach my test-program, which compares master (this morning) with 
covered_indexes (warning: it takes a while to generate the used tables).

The test tables are created as:
   create table $t (c1 int, c2 int, c3 int, c4 int);
   insert into $t (select x, 2*x, 3*x, 4 from generate_series(1, 
$rowcount) as x);
   create unique index ${t}uniqueinclude_idx on $t using btree (c1, c2) 
include (c3, c4);

or for HEAD, just:
   create unique index ${t}unique_idx on $t using btree (c1, c2);


Here is typical output (edited a bit to prevent email-mangling):

test1:
-- explain analyze select c1, c2 from nt0___100000000 where c1 < 10000   
-- 250x
unpatched 6511: 100M rows Execution Time:  (normal/normal)  98 %  exec 
avg: 2.44
   patched 6976: 100M rows Execution Time: (covered/normal) 108 %  exec 
avg: 2.67
                                                        test1 patched / 
unpatched: 109.49 %

test4:
-- explain analyze select c1, c2 from nt0___100000000 where c1 < 10000 
and c3 < 20
unpatched 6511: 100M rows Execution Time:  (normal/normal)  95 %    exec 
avg: 1.56
   patched 6976: 100M rows Execution Time: (covered/normal)  60 %    exec 
avg: 0.95
                                                        test4 patched / 
unpatched:  60.83 %


So the main good thing is that 60%, a good improvement -- but that ~109% 
(a slow-down) is also quite repeatable.

(there are a more goodies from the patch (like improved insert-speed) 
but I just wanted to draw attention to this particular slow-down too)

I took all timings from explain analyze versions of the statements, on 
the assumption that that would be quite comparable to 'normal' querying. 
(please let me know if that introduces error).


# \dti+ nt0___1*
                                            List of relations
  Schema |               Name               | Type  |  Owner   |      
Table      |  Size
--------+----------------------------------+-------+----------+-----------------+--------
  public | nt0___100000000                  | table | aardvark |          
        | 4224 MB
  public | nt0___100000000uniqueinclude_idx | index | aardvark | 
nt0___100000000 | 3004 MB


(for what it's worth, I'm in favor of getting this patch into v11 
although I can't say I followed the technical details too much)


thanks,


Erik Rijkers