spgist-radix-test.sql

create table urls_short (url varchar, inc1 int, inc2 float, inc3 varchar, inc4 int, inc5 char, inc6 varchar, inc7 varchar, inc8 float, inc9 float, inc10 int);
copy urls_short (url) from 'urls-short.txt';

update urls_short set inc1 = length (url);
update urls_short set inc2 = 2* length (url);
update urls_short set inc3 = upper(url);
update urls_short set inc4 = 3* length (url);
update urls_short set inc5 = left (url, 1);
update urls_short set inc6 = left (url, 3);
update urls_short set inc7 = left (url, 4);
update urls_short set inc8 = 3.14* length (url);
update urls_short set inc9 = 2.7* length (url);
update urls_short set inc10 = 5* length (url);


CREATE INDEX spgist_radix_short_spgist ON urls_short USING spgist (url) INCLUDE (inc1, inc2, inc3, inc4, inc5, inc6, inc7, inc8, inc9, inc10);
set enable_seqscan = off;

explain select * from urls_short where url ^@ 'http://www.fr' order by inc10 limit 1000;

update urls_short set inc1=NULL where (random() > 0.8);
update urls_short set inc2=NULL where (random() > 0.8);
update urls_short set inc3=NULL where (random() > 0.8);
update urls_short set inc4=NULL where (random() > 0.8);
update urls_short set inc5=NULL where (random() > 0.8);
update urls_short set inc6=NULL where (random() > 0.8);
update urls_short set inc7=NULL where (random() > 0.8);
update urls_short set inc8=NULL where (random() > 0.8);
update urls_short set inc9=NULL where (random() > 0.8);
update urls_short set inc10=NULL where (random() > 0.8);

explain analyse select * from urls_short where url ^@ 'http://www.fr' order by inc10 limit 1000;
select * from urls_short where url ^@ 'http://www.fr' order by inc10 limit 1000;