Horton

Re: WIP: Covering + unique indexes.

David Rowley <david.rowley@2ndquadrant.com>

View thread View on postgr.es
From: David Rowley <david.rowley@2ndquadrant.com>
To: Anastasia Lubennikova <a.lubennikova@postgrespro.ru>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2016-01-06T07:55:22Z
Lists: pgsql-hackers 
On 4 January 2016 at 21:49, David Rowley <david.rowley@2ndquadrant.com>
wrote:

> I've not tested the patch yet. I will send another email soon with the
> results of that.
>

Hi,

As promised I've done some testing on this, and I've found something which
is not quite right:

create table ab (a int,b int);
insert into ab select x,y from generate_series(1,20) x(x),
generate_series(10,1,-1) y(y);
create index on ab (a) including (b);
explain select * from ab order by a,b;
                        QUERY PLAN
----------------------------------------------------------
 Sort  (cost=10.64..11.14 rows=200 width=8)
   Sort Key: a, b
   ->  Seq Scan on ab  (cost=0.00..3.00 rows=200 width=8)
(3 rows)

This is what I'd expect

truncate table ab;
insert into ab select x,y from generate_series(1,20) x(x),
generate_series(10,1,-1) y(y);
explain select * from ab order by a,b;
                                  QUERY PLAN

------------------------------------------------------------------------------
 Index Only Scan using ab_a_b_idx on ab  (cost=0.15..66.87 rows=2260
width=8)
(1 row)

This index, as we've defined it should not be able to satisfy the query's
order by, although it does give correct results, that's because the index
seems to be built wrongly in cases where the rows are added after the index
exists.

If we then do:

reindex table ab;
explain select * from ab order by a,b;
                        QUERY PLAN
----------------------------------------------------------
 Sort  (cost=10.64..11.14 rows=200 width=8)
   Sort Key: a, b
   ->  Seq Scan on ab  (cost=0.00..3.00 rows=200 width=8)
(3 rows)

It looks normal again.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Commits

  1. Adjust INCLUDE index truncation comments and code.

  2. Add commentary explaining why MaxIndexTuplesPerPage calculation is safe.

  3. Indexes with INCLUDE columns and their support in B-tree

  4. Add amcheck verification of heap relations belonging to btree indexes.

  5. Doc: move info for btree opclass implementors into main documentation.

  6. Doc: mention that you can't PREPARE TRANSACTION after NOTIFY.

  7. Remove dedicated B-tree root-split record types.

  8. Restructure index access method API to hide most of it at the C level.

  9. Split _bt_insertonpg to two functions.

  10. Major overhaul of btree index code. Eliminate special BTP_CHAIN logic for