PostGIS Performance

Reallie |

Winnie |

This project is an attempt at building a performance test suite for PostGIS.

Requirements for running:

• A working PostgreSQL install that supports versions to test
• All dependencies needed to install PostGIS
• At least one version of PostGIS.
• Perl
• pgTap extension
• pg_prove Perl extension
• make

Goals:

1. Be able to detect regressions in performance from PostGIS present to previous versions
2. Be able to test with real-world data and actual ways people are using PostGIS.

The project is divided into several folders (may be reorganized later)

1. ci folder -- each folder represents a ci bot and scripts needed to have the bot (build postgis, some) and run the tests
2. pgtap - contains pgtap tests divided into folders by postgis extension. Each file contains one or more tests.

• run_template.sh -- this is a sample script you can copy from for your needs. Copy to a file run_tests_local.sh and make custom changes as needed. The run_tests_local.sh is ignored by git.
• Makefile - Has the core commands
• The ci bots test multiple versions of PostGIS and build PostGIS versions as needed, take a look at ci/reallie/run_tests.sh for an example

For help run:

make help

outputs:

help                           Show this help screen
download-data                  Downloads all data files from postgis.net. Puts in folder defined by env $DOWNLOAD_DATADIR
clean                          Delete generated files
prep-postgis                   Create database and load with data
test-postgis                   Run postgis tests
prep-tiger-geocoder            Create tiger database and load with data
test-tiger-geocoder            Run tiger geocoder tests
prep-topology                  Create topology database and load with data
test-topology                  Run topology tests
prep-raster                    Create database for raster tests and load with data
test-raster                    Run raster tests
prep-sfcgal                    Create database for sfcgal tests and load with data
test-sfcgal                    Run sfcgal tests

How to experiment with one test

When developing tests you might want to focus your attention on one test. Here is how you run for a specific test:

export POSTGIS_PERF_DB=postgis_topo_perf #define custom db for experimenting
make prep-topology  #only need to do this once, can reuse across custom tests
pg_prove --failures --verbose -d ${POSTGIS_PERF_DB} pgtap/topology/totopogeom.sql

You might also want to focus on a suite of tests

export POSTGIS_PERF_DB=postgis_perf #define custom db for experimenting
make prep-postgis
  pg_prove --failures --timer --comments --verbose -d ${POSTGIS_PERF_DB} pgtap/postgis/01*.sql pgtap/postgis/03*.sql pgtap/postgis/04*.sql

Data used in these tests is store at http://postgis.net/extra/test-data

Data in http://postgis.net/extra/test-data is currently sourced from Census Tiger, OSM data, and Natural Earth data sources and is packaged in a format suitable for load by psql.

Look at sample script run_template.sh for variables you can override to control behavior.

The data sources available there are as follows:

tiger_national.sql.bz2 - this requires the postgis_tiger_geocoder extension to be loaded before hand as it inherits from the tables packaged with that. The data is from https://www2.census.gov/geo/tiger/TIGER2020/ (zcta5, state, county)

tiger_dc.sql.bz2 - this requires the postgis_tiger_geocoder extension. Data sourced from https://www2.census.gov/geo/tiger/TIGER2020//tl_2020_11_*.zip files

tiger_ma.sql.bz2 - this requires the postgis_tiger_geocoder extension. Data sourced from https://www2.census.gov/geo/tiger/TIGER2020//tl_2020_25_*.zip files

osm_belarus.sql.bz2 - this is sourced from https://download.geofabrik.de/europe/belarus-latest.osm.pbf dated 2021-06-28T20:21:42Z

osm_china.sql.bz2 - this is sourced from https://download.geofabrik.de/asia/china-140101-free.shp.zip dated 2018-05-03 16:26

countries.sql.bz2 - this is sourced from https://www.naturalearthdata.com/http//www.naturalearthdata.com/download/10m/cultural/ne_10m_admin_0_countries.zip (4.1.0) 2021-08-27