#!/bin/bash

echo "
drop   table if exists quad_poly_tbl ;
create table           quad_poly_tbl (id int, p polygon);
insert into quad_poly_tbl
  select (x - 1) * 100 + y, polygon(circle(point(x * 10, y * 10), 1 + (x + y) % 10))
  from generate_series(1, 100) x, generate_series(1, 100) y ;
insert into quad_poly_tbl
  select i, polygon '((200, 300),(210, 310),(230, 290))'
  from generate_series(10001, 11000) AS i ;

analyze quad_poly_tbl;

-- search for overlap with this polygon:
select * from quad_poly_tbl where p && '((22,640),(23.0717967697245,644),(26,646.928203230275),(30,648),(34,646.928203230275),(36.9282032302755,644),(38,640))'::polygon ;

--> Time: 1.382 ms  -- Seq Scan on quad_poly_tbl (3 MB)

-- now add index:
create index quad_poly_tbl_idx ON quad_poly_tbl USING spgist(p);

-- search again for overlap with this polygon but now WITH the polygon-index present:
select * from quad_poly_tbl where p && '((22,640),(23.0717967697245,644),(26,646.928203230275),(30,648),(34,646.928203230275),(36.9282032302755,644),(38,640))'::polygon ;

--> Time: 0.271 ms  -- Bitmap Index Scan on quad_poly_tbl_idx (1 MB)

" | psql -qa