Re: WIP: Covering + unique indexes. (the good and the bad)

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