Skip to content

Instantly share code, notes, and snippets.

@fcurella

fcurella/psql.txt

Created August 9, 2012 20:56
Show Gist options
  • Save fcurella/3307966 to your computer and use it in GitHub Desktop.
Save fcurella/3307966 to your computer and use it in GitHub Desktop.
Download ZIP
postgis 2.0 index benchmark
Raw
psql.txt
$ createdb indextest -T template_postgis
$ psql indextest
psql (9.1.3)
Type "help" for help.
indextest=# create table random_points (
indextest(# id integer primary key,
indextest(# pt geometry
indextest(# );
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "random_points_pkey" for table "random_points"
CREATE TABLE
indextest=# insert into random_points
indextest-# select generate_series as id, st_makepoint(1000000.0 * random(),
indextest(# 1000000.0 * random(), 1000000.0 * random()) from
indextest-# generate_series(1,1000000);
INSERT 0 1000000
indextest=# create index rp_gix on random_points using gist (pt);
CREATE INDEX
indextest=# explain analyze select count(*) from random_points where pt &&
indextest-# 'linestring(10000 10000, 15000 15000)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=55.11..55.12 rows=1 width=0) (actual time=0.334..0.334 rows=1 loops=1)
-> Bitmap Heap Scan on random_points (cost=4.56..55.08 rows=13 width=0) (actual time=0.139..0.323 rows=26 loops=1)
Recheck Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix (cost=0.00..4.55 rows=13 width=0) (actual time=0.122..0.122 rows=26 loops=1)
Index Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.485 ms
(6 rows)
indextest=# explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=55.11..55.12 rows=1 width=0) (actual time=0.137..0.137 rows=1 loops=1)
-> Bitmap Heap Scan on random_points (cost=4.56..55.08 rows=13 width=0) (actual time=0.105..0.129 rows=26 loops=1)
Recheck Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix (cost=0.00..4.55 rows=13 width=0) (actual time=0.096..0.096 rows=26 loops=1)
Index Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.188 ms
(6 rows)
indextest=# explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=55.11..55.12 rows=1 width=0) (actual time=0.213..0.213 rows=1 loops=1)
-> Bitmap Heap Scan on random_points (cost=4.56..55.08 rows=13 width=0) (actual time=0.164..0.202 rows=26 loops=1)
Recheck Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix (cost=0.00..4.55 rows=13 width=0) (actual time=0.152..0.152 rows=26 loops=1)
Index Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.264 ms
(6 rows)
indextest=#
-------
$ createdb indextest
$ psql indextest
psql (9.1.3, server 9.1.4)
Type "help" for help.
indextest=# CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION
indextest=# create table random_points (
indextest(# id integer primary key,
indextest(# pt geometry
indextest(# );
CREATE TABLE
indextest=# insert into random_points
indextest-# select generate_series as id, st_makepoint(1000000.0 * random(),
indextest(# 1000000.0 * random(), 1000000.0 * random()) from
indextest-# generate_series(1,1000000);
INSERT 0 1000000
indextest=# explain analyze select count(*) from random_points where pt &&
indextest-# 'linestring(10000 10000, 15000 15000)';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=23715.49..23715.50 rows=1 width=0) (actual time=766.325..766.325 rows=1 loops=1)
-> Seq Scan on random_points (cost=0.00..23715.47 rows=6 width=0) (actual time=30.400..766.266 rows=19 loops=1)
Filter: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 770.923 ms
(4 rows)
indextest=# explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21846.13..21846.14 rows=1 width=0) (actual time=491.250..491.250 rows=1 loops=1)
-> Seq Scan on random_points (cost=0.00..21846.00 rows=53 width=0) (actual time=14.254..491.205 rows=19 loops=1)
Filter: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 491.312 ms
(4 rows)
indextest=# explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21846.13..21846.14 rows=1 width=0) (actual time=486.877..486.877 rows=1 loops=1)
-> Seq Scan on random_points (cost=0.00..21846.00 rows=53 width=0) (actual time=14.373..486.839 rows=19 loops=1)
Filter: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 486.932 ms
(4 rows)
indextest=# create index rp_gix on random_points using gist (pt);
CREATE INDEX
indextest=# explain analyze select count(*) from random_points where pt &&
indextest-# 'linestring(10000 10000, 15000 15000)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=157.53..157.54 rows=1 width=0) (actual time=46.416..46.416 rows=1 loops=1)
-> Bitmap Heap Scan on random_points (cost=4.78..157.43 rows=40 width=0) (actual time=19.536..46.395 rows=19 loops=1)
Recheck Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix (cost=0.00..4.77 rows=40 width=0) (actual time=0.432..0.432 rows=19 loops=1)
Index Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 90.344 ms
(6 rows)
indextest=# explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=157.53..157.54 rows=1 width=0) (actual time=0.561..0.561 rows=1 loops=1)
-> Bitmap Heap Scan on random_points (cost=4.78..157.43 rows=40 width=0) (actual time=0.533..0.558 rows=19 loops=1)
Recheck Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix (cost=0.00..4.77 rows=40 width=0) (actual time=0.522..0.522 rows=19 loops=1)
Index Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.617 ms
(6 rows)
indextest=# explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=157.53..157.54 rows=1 width=0) (actual time=0.387..0.387 rows=1 loops=1)
-> Bitmap Heap Scan on random_points (cost=4.78..157.43 rows=40 width=0) (actual time=0.353..0.377 rows=19 loops=1)
Recheck Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix (cost=0.00..4.77 rows=40 width=0) (actual time=0.344..0.344 rows=19 loops=1)
Index Cond: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.433 ms
(6 rows)
indextest=# create table random_points_nd (
id integer primary key,
pt geometry
);
CREATE TABLE
indextest=# insert into random_points_nd
select generate_series as id, st_makepoint(1000000.0 * random(),
1000000.0 * random(), 1000000.0 * random()) from
generate_series(1,1000000);
INSERT 0 1000000
indextest=# create index rp_gix_nd on random_points_nd using gist (pt gist_geometry_ops_nd);
CREATE INDEX
indextest=# explain analyze select count(*) from random_points_nd where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21846.07..21846.08 rows=1 width=0) (actual time=634.945..634.946 rows=1 loops=1)
-> Seq Scan on random_points_nd (cost=0.00..21846.00 rows=27 width=0) (actual time=33.082..634.896 rows=16 loops=1)
Filter: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 635.001 ms
(4 rows)
indextest=# explain analyze select count(*) from random_points_nd where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21846.07..21846.08 rows=1 width=0) (actual time=641.926..641.926 rows=1 loops=1)
-> Seq Scan on random_points_nd (cost=0.00..21846.00 rows=27 width=0) (actual time=40.894..641.878 rows=16 loops=1)
Filter: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 641.972 ms
(4 rows)
indextest=# explain analyze select count(*) from random_points_nd where pt &&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21846.07..21846.08 rows=1 width=0) (actual time=490.994..490.994 rows=1 loops=1)
-> Seq Scan on random_points_nd (cost=0.00..21846.00 rows=27 width=0) (actual time=33.000..490.956 rows=16 loops=1)
Filter: (pt && '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 491.044 ms
(4 rows)
indextest=# # DROP TABLE random_points_nd;
DROP TABLE
indextest=# create table random_points_nd (
id integer primary key,
pt geometry
);
CREATE TABLE
indextest=# insert into random_points_nd
indextest-# select generate_series as id, st_makepoint(1000000.0 * random(),
indextest(# 1000000.0 * random(), 1000000.0 * random()) from
indextest-# generate_series(1,1000000);
INSERT 0 1000000
indextest=# create index rp_gix_nd on random_points_nd using gist (pt gist_geometry_ops_nd);
CREATE INDEX
indextest=# explain analyze select count(*) from random_points_nd where pt &&&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2954.73..2954.74 rows=1 width=0) (actual time=0.036..0.036 rows=1 loops=1)
-> Bitmap Heap Scan on random_points_nd (cost=48.37..2952.23 rows=1000 width=0) (actual time=0.033..0.033 rows=0 loops=1)
Recheck Cond: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix_nd (cost=0.00..48.12 rows=1000 width=0) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.088 ms
(6 rows)
indextest=# explain analyze select count(*) from random_points_nd where pt &&&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2954.73..2954.74 rows=1 width=0) (actual time=0.045..0.045 rows=1 loops=1)
-> Bitmap Heap Scan on random_points_nd (cost=48.37..2952.23 rows=1000 width=0) (actual time=0.042..0.042 rows=0 loops=1)
Recheck Cond: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix_nd (cost=0.00..48.12 rows=1000 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Index Cond: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.095 ms
indextest=# explain analyze select count(*) from random_points_nd where pt &&&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=2954.73..2954.74 rows=1 width=0) (actual time=0.042..0.042 rows=1 loops=1)
-> Bitmap Heap Scan on random_points_nd (cost=48.37..2952.23 rows=1000 width=0) (actual time=0.039..0.039 rows=0 loops=1)
Recheck Cond: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
-> Bitmap Index Scan on rp_gix_nd (cost=0.00..48.12 rows=1000 width=0) (actual time=0.037..0.037 rows=0 loops=1)
Index Cond: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 0.091 ms
(6 rows)
----
$ dropdb indextest
$ createdb indextest
$ psql indextest
psql (9.1.3, server 9.1.4)
Type "help" for help.
indextest=# CREATE EXTENSION postgis; CREATE EXTENSION postgis_topology;
CREATE EXTENSION
CREATE EXTENSION
indextest=# create table random_points (
indextest(# id integer primary key,
indextest(# pt geometry
indextest(# );
CREATE TABLE
indextest=# insert into random_points
indextest-# select generate_series as id, st_makepoint(1000000.0 * random(),
indextest(# 1000000.0 * random(), 1000000.0 * random()) from
indextest-# generate_series(1,1000000);
INSERT 0 1000000
indextest=# create index rp_gix on random_points using gist (pt);
CREATE INDEX
indextest-# explain analyze select count(*) from random_points where pt &&&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21848.50..21848.51 rows=1 width=0) (actual time=569.241..569.241 rows=1 loops=1)
-> Seq Scan on random_points (cost=0.00..21846.00 rows=1000 width=0) (actual time=569.234..569.234 rows=0 loops=1)
Filter: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 569.312 ms
(4 rows)
indextest=# explain analyze select count(*) from random_points where pt &&&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21848.50..21848.51 rows=1 width=0) (actual time=571.569..571.569 rows=1 loops=1)
-> Seq Scan on random_points (cost=0.00..21846.00 rows=1000 width=0) (actual time=571.564..571.564 rows=0 loops=1)
Filter: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 571.616 ms
(4 rows)
indextest=# explain analyze select count(*) from random_points where pt &&&
'linestring(10000 10000, 15000 15000)';
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=21848.50..21848.51 rows=1 width=0) (actual time=575.382..575.382 rows=1 loops=1)
-> Seq Scan on random_points (cost=0.00..21846.00 rows=1000 width=0) (actual time=575.377..575.377 rows=0 loops=1)
Filter: (pt &&& '010200000002000000000000000088C340000000000088C34000000000004CCD4000000000004CCD40'::geometry)
Total runtime: 575.430 ms
(4 rows)
Raw
summary.txt
PostGIS 1.5
===========
regular gist index
------------------
0.485 ms
0.188 ms
0.264 ms
PostGIS 2.0
===========
no index:
---------
770.923 ms
491.312 ms
486.932 ms
regular gist index:
-------------------
90.344 ms
0.617 ms
0.433 ms
index 'gist_geometry_ops_nd'
----------------------------
635.001 ms
641.972 ms
491.044 ms
index 'gist_geometry_ops_nd', operator &&&
------------------------------------------
0.088 ms
0.095 ms
0.091 ms
regular gist index, operator &&&
--------------------------------
569.312 ms
571.616 ms
575.430 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment