Wiki @ SAI Virtual Observatory

This is an old revision of the document!

dropdb q3ctest
createdb q3ctest

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

-- Static data
SELECT generate_series(1,1000000) AS id, random()*360 AS ra, (random()-0.5)*180 AS dec INTO test_q3c;
SELECT generate_series(1,1000000) AS id, spoint(random()*2*3.1415926, (random()-0.5)*3.1415926) AS point INTO test_pgsphere;

-- Index on static data
CREATE INDEX test_q3c_idx ON test_q3c (q3c_ang2ipix(ra, dec));
CREATE INDEX test_pgsphere_idx ON test_pgsphere USING gist(point);

-- Conesearch on static data
SELECT count(*) FROM test_q3c WHERE q3c_radial_query(ra, dec, 0, 0, 10);
SELECT count(*) FROM test_pgsphere where point @ scircle '<(0, 0), 10d>';

-- Live update / preparation 
SELECT generate_series(1,1000000) AS id, NULL::float AS ra, NULL::float AS dec INTO running_q3c;
CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
SELECT generate_series(1,1000000) as id, NULL::spoint as point into running_pgsphere;
CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);

-- Live update - run
UPDATE running_q3c SET (ra, dec) = (random()*360, (random()-0.5)*180);
UPDATE running_pgsphere SET point = spoint(random()*2*3.1415926, (random()-0.5)*3.1415926);

q3ctest=# \timing
Timing is on.
q3ctest=# -- Static data
q3ctest=# SELECT generate_series(1,1000000) AS id, random()*360 AS ra, (random()-0.5)*180 AS dec INTO test_q3c;
SELECT 1000000
Time: 7324.102 ms
q3ctest=# SELECT generate_series(1,1000000) AS id, spoint(random()*2*3.1415926, (random()-0.5)*3.1415926) AS point INTO test_pgsphere;
SELECT 1000000
Time: 8886.958 ms
q3ctest=#
q3ctest=# -- Index on static data
q3ctest=# CREATE INDEX test_q3c_idx ON test_q3c (q3c_ang2ipix(ra, dec));
CREATE INDEX
Time: 11210.999 ms
q3ctest=# CREATE INDEX test_pgsphere_idx ON test_pgsphere USING gist(point);
CREATE INDEX
Time: 192927.871 ms
q3ctest=#
q3ctest=# -- Conesearch on static data
q3ctest=# SELECT count(*) FROM test_q3c WHERE q3c_radial_query(ra, dec, 0, 0, 10);
 count
-------
  4879
(1 row)

Time: 943.568 ms
q3ctest=# SELECT count(*) FROM test_pgsphere where point @ scircle '<(0, 0), 10d>';
 count
-------
  4909
(1 row)

Time: 178.119 ms
q3ctest=#
q3ctest=# -- Live update / preparation
q3ctest=# SELECT generate_series(1,1000000) AS id, NULL::float AS ra, NULL::float AS dec INTO running_q3c;
SELECT 1000000
Time: 5435.335 ms
q3ctest=# CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
CREATE INDEX
Time: 3460.449 ms
q3ctest=# SELECT generate_series(1,1000000) as id, NULL::spoint as point into running_pgsphere;
SELECT 1000000
Time: 4895.334 ms
q3ctest=# CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);
CREATE INDEX
Time: 25023.983 ms
q3ctest=#
q3ctest=# -- Live update - run
q3ctest=# UPDATE running_q3c SET (ra, dec) = (random()*360, (random()-0.5)*180);
UPDATE 1000000
Time: 58032.557 ms
q3ctest=# UPDATE running_pgsphere SET point = spoint(random()*2*3.1415926, (random()-0.5)*3.1415926);
UPDATE 1000000
Time: 249849.524 ms