test_including.sql

create extension pageinspect;
drop table tbl;
create table tbl (id int, data int);
insert into tbl values (0,1);
insert into tbl values (0,5);
insert into tbl values (0,3);
select ctid,* from tbl ;
create index idx on tbl (id) including (data);
/*
 * items on the index page are ordered by id,
 * but NOT ordered by the data attribute.
 */
select * from bt_page_items('idx',1);

insert into tbl select 0, 5 from generate_series(0,400);
select * from bt_metap('idx');
select * from bt_page_items('idx',1) order by itemoffset  limit 2;
select * from bt_page_items('idx',1) order by itemoffset desc limit 2;

insert into tbl select 0, 3 from generate_series(0,30);
select * from bt_metap('idx');
/*
 * 1. items, spreading on the leaf pages, are ordered by id,
 * but NOT ordered by the data attribute.
 * 2. inner page item and highkey at the page 1 contain truncated tuples
 */
select * from bt_page_items('idx', 3);
select * from bt_page_items('idx', 2);
select * from bt_page_items('idx', 1);

vacuum analyze tbl;

explain analyze select count(*) from tbl where data = 3;
select count(*) from tbl where  = 3;

set enable_seqscan to off;
/* Bitmap index scan successfully returns the same result as Seq scan */
explain analyze select count(*) from tbl where  = 3;
select count(*) from tbl where  = 3;

/*   Index Cond: (a = 0)   Filter: (b = 1)  */
explain analyze select data from tbl where id = 0 and data = 1;