Wiki @ SAI Virtual Observatory

dropdb q3ctest
createdb q3ctest

psql q3ctest < /usr/local/pgsql/share/contrib/q3c.sql
psql q3ctest < /usr/local/pgsql/share/contrib/pg_sphere.sql

\timing on
DROP TABLE running_q3c;
DROP TABLE running_pgsphere;

CREATE TABLE running_q3c (id INT, ra FLOAT, dec FLOAT);
CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
CREATE TABLE running_pgsphere (id INT, point spoint);
CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);

-- Streaming data with index update
INSERT INTO running_q3c (id, ra, dec) (SELECT generate_series(1,100000), random()*360, (random()-0.5)*180);
INSERT INTO running_pgsphere (id, point) (SELECT generate_series(1,100000), spoint(random()*2*pi(), (random()-0.5)*pi()));
-- Once more
INSERT INTO running_pgsphere (id, point) (SELECT generate_series(1,100000), spoint(random()*2*pi(), (random()-0.5)*pi()));
INSERT INTO running_q3c (id, ra, dec) (SELECT generate_series(1,100000), random()*360, (random()-0.5)*180);

DROP INDEX running_q3c_idx;
DROP INDEX running_pgsphere_idx;

-- Index creation on static data
CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);

DROP TABLE test_res_q3c;
DROP TABLE test_res_pgsphere;

-- Conesearch on static data
SELECT (SELECT count(*) FROM running_q3c WHERE q3c_radial_query(ra, dec, ra0, dec0, 1)) INTO test_res_q3c FROM (SELECT generate_series(1,1000), random()*360 AS ra0, (random()-0.5)*180 AS dec0) s;
SELECT (SELECT count(*) FROM running_pgsphere WHERE point @ scircle(spoint(ra0, dec0), 1*pi()/180)) INTO test_res_pgsphere FROM (SELECT generate_series(1,1000), random()*2*pi() AS ra0, (random()-0.5)*pi() AS dec0) s;

DROP TABLE test_res_q3c;
DROP TABLE test_res_pgsphere;

-- Once more
SELECT (SELECT count(*) FROM running_pgsphere WHERE point @ scircle(spoint(ra0, dec0), 1*pi()/180)) INTO test_res_pgsphere FROM (SELECT generate_series(1,1000), random()*2*pi() AS ra0, (random()-0.5)*pi() AS dec0) s;
SELECT (SELECT count(*) FROM running_q3c WHERE q3c_radial_query(ra, dec, ra0, dec0, 1)) INTO test_res_q3c FROM (SELECT generate_series(1,1000), random()*360 AS ra0, (random()-0.5)*180 AS dec0) s;

q3ctest=# DROP TABLE test_res_q3c;
DROP TABLE
Time: 384.133 ms
q3ctest=# DROP TABLE running_pgsphere;
DROP TABLE
Time: 145.407 ms
q3ctest=#
q3ctest=# CREATE TABLE running_q3c (id INT, ra FLOAT, dec FLOAT);
CREATE TABLE
Time: 81.882 ms
q3ctest=# CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
CREATE INDEX
Time: 62.291 ms
q3ctest=# CREATE TABLE running_pgsphere (id INT, point spoint);
CREATE TABLE
Time: 7.611 ms
q3ctest=# CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);
CREATE INDEX
Time: 72.616 ms
q3ctest=#
q3ctest=# -- Streaming data with index update
q3ctest=# INSERT INTO running_q3c (id, ra, dec) (SELECT generate_series(1,100000), random()*360, (random()-0.5)*180);
INSERT 0 100000
Time: 3811.922 ms
q3ctest=# INSERT INTO running_pgsphere (id, point) (SELECT generate_series(1,100000), spoint(random()*2*pi(), (random()-0.5)*pi()));
INSERT 0 100000
Time: 18366.058 ms
q3ctest=# -- Once more
q3ctest=# INSERT INTO running_pgsphere (id, point) (SELECT generate_series(1,100000), spoint(random()*2*pi(), (random()-0.5)*pi()));
INSERT 0 100000
Time: 21094.975 ms
q3ctest=# INSERT INTO running_q3c (id, ra, dec) (SELECT generate_series(1,100000), random()*360, (random()-0.5)*180);
INSERT 0 100000
Time: 3794.624 ms
q3ctest=#
q3ctest=# DROP INDEX running_q3c_idx;
DROP INDEX
Time: 11.024 ms
q3ctest=# DROP INDEX running_pgsphere_idx;
DROP INDEX
Time: 10.853 ms
q3ctest=#
q3ctest=# -- Index creation on static data
q3ctest=# CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
CREATE INDEX
Time: 1623.090 ms
q3ctest=# CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);
CREATE INDEX
Time: 30852.003 ms
q3ctest=#
q3ctest=# DROP TABLE test_res_q3c;
DROP TABLE
Time: 19.148 ms
q3ctest=# DROP TABLE test_res_pgsphere;
DROP TABLE
Time: 5.124 ms
q3ctest=#
q3ctest=# -- Conesearch on static data
q3ctest=# SELECT (SELECT count(*) FROM running_q3c WHERE q3c_radial_query(ra, dec, ra0, dec0, 1)) INTO test_res_q3c FROM (SELECT generate_series(1,1000), random()*360 AS ra0, (random()-0.5)*180 AS dec0) s;
SELECT 1000
Time: 9450.178 ms
q3ctest=# SELECT (SELECT count(*) FROM running_pgsphere WHERE point @ scircle(spoint(ra0, dec0), 1*pi()/180)) INTO test_res_pgsphere FROM (SELECT generate_series(1,1000), random()*2*pi() AS ra0, (random()-0.5)*pi() AS dec0) s;
SELECT 1000
Time: 1333.480 ms
q3ctest=#
q3ctest=# DROP TABLE test_res_q3c;
DROP TABLE
Time: 3.445 ms
q3ctest=# DROP TABLE test_res_pgsphere;
DROP TABLE
Time: 3.588 ms
q3ctest=#
q3ctest=# -- Once more
q3ctest=# SELECT (SELECT count(*) FROM running_pgsphere WHERE point @ scircle(spoint(ra0, dec0), 1*pi()/180)) INTO test_res_pgsphere FROM (SELECT generate_series(1,1000), random()*2*pi() AS ra0, (random()-0.5)*pi() AS dec0) s;
SELECT 1000
Time: 1058.741 ms
q3ctest=# SELECT (SELECT count(*) FROM running_q3c WHERE q3c_radial_query(ra, dec, ra0, dec0, 1)) INTO test_res_q3c FROM (SELECT generate_series(1,1000), random()*360 AS ra0, (random()-0.5)*180 AS dec0) s;
SELECT 1000
Time: 8666.836 ms

Back to top