(8 rows)

drop table idxpart;

-- test fastpath mechanism for index insertion

create table fastpath (a int, b text, c numeric);

create unique index fpindex1 on fastpath(a);

insert into fastpath values (1, 'b1', 100.00);

insert into fastpath values (1, 'b1', 100.00); -- unique key check

ERROR: duplicate key value violates unique constraint "fpindex1"

DETAIL: Key (a)=(1) already exists.

truncate fastpath;

insert into fastpath select generate_series(1,10000), 'b', 100;

-- vacuum the table so as to improve chances of index-only scans. we can't

-- guarantee if index-only scans will be picked up in all cases or not, but

-- that fuzziness actually helps the test.

vacuum fastpath;

set enable_seqscan to false;

set enable_bitmapscan to false;

select sum(a) from fastpath where a = 6456;

sum

------

6456

(1 row)

select sum(a) from fastpath where a >= 5000 and a < 5700;

sum

---------

3744650

(1 row)

-- drop the only index on the table and compute hashes for

-- a few queries which orders the results in various different ways.

drop index fpindex1;

truncate fastpath;

insert into fastpath select y.x, 'b' || (y.x/10)::text, 100 from (select generate_series(1,10000) as x) y;

select md5(string_agg(a::text, b order by a, b asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

select md5(string_agg(a::text, b order by a desc, b desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

6167a852b3e0679886b84a5405b5b53d

(1 row)

select md5(string_agg(a::text, b order by b, a desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

dfcf2bd5e5fea8397d47b2fd14618d31

(1 row)

select md5(string_agg(a::text, b order by b, a asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

-- now create a multi-column index with both column asc

create index fpindex2 on fastpath(a, b);

truncate fastpath;

insert into fastpath select y.x, 'b' || (y.x/10)::text, 100 from (select generate_series(1,10000) as x) y;

-- again, vacuum here either forces index-only scans or creates fuzziness

vacuum fastpath;

select md5(string_agg(a::text, b order by a, b asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

select md5(string_agg(a::text, b order by a desc, b desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

6167a852b3e0679886b84a5405b5b53d

(1 row)

select md5(string_agg(a::text, b order by b, a desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

dfcf2bd5e5fea8397d47b2fd14618d31

(1 row)

select md5(string_agg(a::text, b order by b, a asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

-- same queries with a different kind of index now. the final result must not

-- change irrespective of what kind of index we have.

drop index fpindex2;

create index fpindex3 on fastpath(a desc, b asc);

truncate fastpath;

insert into fastpath select y.x, 'b' || (y.x/10)::text, 100 from (select generate_series(1,10000) as x) y;

vacuum fastpath;

select md5(string_agg(a::text, b order by a, b asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

select md5(string_agg(a::text, b order by a desc, b desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

6167a852b3e0679886b84a5405b5b53d

(1 row)

select md5(string_agg(a::text, b order by b, a desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

dfcf2bd5e5fea8397d47b2fd14618d31

(1 row)

select md5(string_agg(a::text, b order by b, a asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

-- repeat again

drop index fpindex3;

create index fpindex4 on fastpath(a asc, b desc);

truncate fastpath;

insert into fastpath select y.x, 'b' || (y.x/10)::text, 100 from (select generate_series(1,10000) as x) y;

vacuum fastpath;

select md5(string_agg(a::text, b order by a, b asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

select md5(string_agg(a::text, b order by a desc, b desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

6167a852b3e0679886b84a5405b5b53d

(1 row)

select md5(string_agg(a::text, b order by b, a desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

dfcf2bd5e5fea8397d47b2fd14618d31

(1 row)

select md5(string_agg(a::text, b order by b, a asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

-- and again, this time indexing by (b, a). Note that column "b" has non-unique

-- values.

drop index fpindex4;

create index fpindex5 on fastpath(b asc, a desc);

truncate fastpath;

insert into fastpath select y.x, 'b' || (y.x/10)::text, 100 from (select generate_series(1,10000) as x) y;

vacuum fastpath;

select md5(string_agg(a::text, b order by a, b asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

select md5(string_agg(a::text, b order by a desc, b desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

6167a852b3e0679886b84a5405b5b53d

(1 row)

select md5(string_agg(a::text, b order by b, a desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

dfcf2bd5e5fea8397d47b2fd14618d31

(1 row)

select md5(string_agg(a::text, b order by b, a asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

-- one last time

drop index fpindex5;

create index fpindex6 on fastpath(b desc, a desc);

truncate fastpath;

insert into fastpath select y.x, 'b' || (y.x/10)::text, 100 from (select generate_series(1,10000) as x) y;

vacuum fastpath;

select md5(string_agg(a::text, b order by a, b asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

select md5(string_agg(a::text, b order by a desc, b desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

6167a852b3e0679886b84a5405b5b53d

(1 row)

select md5(string_agg(a::text, b order by b, a desc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

dfcf2bd5e5fea8397d47b2fd14618d31

(1 row)

select md5(string_agg(a::text, b order by b, a asc)) from fastpath

where a >= 1000 and a < 2000 and b > 'b1' and b < 'b3';

md5

----------------------------------

2ca216010a558a52d7df12f76dfc77ab

(1 row)

drop table fastpath;

-- intentionally leave some objects around

create table idxpart (a int) partition by range (a);

create table idxpart1 partition of idxpart for values from (0) to (100);

select tableoid::regclass,*from idxpartorder by a;

droptable idxpart;

createtablefastpath (aint, btext, cnumeric);

createunique indexfpindex1on fastpath(a);

insert into fastpathvalues (1,'b1',100.00);

insert into fastpathvalues (1,'b1',100.00);-- unique key check

truncate fastpath;

insert into fastpathselect generate_series(1,10000),'b',100;

vacuum fastpath;

set enable_seqscan to false;

set enable_bitmapscan to false;

selectsum(a)from fastpathwhere a=6456;

selectsum(a)from fastpathwhere a>=5000and a<5700;

dropindex fpindex1;

truncate fastpath;

insert into fastpathselecty.x,'b'|| (y.x/10)::text,100from (select generate_series(1,10000)as x) y;

select md5(string_agg(a::text, border by a, basc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by adesc, bdesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, adesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, aasc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

createindexfpindex2on fastpath(a, b);

truncate fastpath;

insert into fastpathselecty.x,'b'|| (y.x/10)::text,100from (select generate_series(1,10000)as x) y;

vacuum fastpath;

select md5(string_agg(a::text, border by a, basc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by adesc, bdesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, adesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, aasc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

dropindex fpindex2;

createindexfpindex3on fastpath(adesc, basc);

truncate fastpath;

insert into fastpathselecty.x,'b'|| (y.x/10)::text,100from (select generate_series(1,10000)as x) y;

vacuum fastpath;

select md5(string_agg(a::text, border by a, basc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by adesc, bdesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, adesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, aasc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

dropindex fpindex3;

createindexfpindex4on fastpath(aasc, bdesc);

truncate fastpath;

insert into fastpathselecty.x,'b'|| (y.x/10)::text,100from (select generate_series(1,10000)as x) y;

vacuum fastpath;

select md5(string_agg(a::text, border by a, basc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by adesc, bdesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, adesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, aasc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

dropindex fpindex4;

createindexfpindex5on fastpath(basc, adesc);

truncate fastpath;

insert into fastpathselecty.x,'b'|| (y.x/10)::text,100from (select generate_series(1,10000)as x) y;

vacuum fastpath;

select md5(string_agg(a::text, border by a, basc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by adesc, bdesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, adesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, aasc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

dropindex fpindex5;

createindexfpindex6on fastpath(bdesc, adesc);

truncate fastpath;

insert into fastpathselecty.x,'b'|| (y.x/10)::text,100from (select generate_series(1,10000)as x) y;

vacuum fastpath;

select md5(string_agg(a::text, border by a, basc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by adesc, bdesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, adesc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

select md5(string_agg(a::text, border by b, aasc))from fastpath

where a>=1000and a<2000and b>'b1'and b<'b3';

droptable fastpath;

createtableidxpart (aint) partition by range (a);

createtableidxpart1 partition of idxpart forvaluesfrom (0) to (100);