Wiki @ SAI Virtual Observatory

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
<code>
dropdb q3ctest
createdb q3ctest

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

<code>
-- Static data
SELECT generate_series(1,1000000) AS id, random()*360 AS ra, (random()-<b style="color:black;background-color:#6666ff">0</b>.5)*180 AS dec INTO test_q3c;
SELECT generate_series(1,1000000) AS id, spoint(random()*2*3.1415926, (random()-<b style="color:black;background-color:#6666ff">0</b>.5)*3.1415926) AS point INTO test_pgsphere;

-- <b style="color:black;background-color:#66ff66">Index</b> on static data
CREATE <b style="color:black;background-color:#66ff66">INDEX</b> test_q3c_idx ON test_q3c (q3c_ang2ipix(ra, dec));
CREATE <b style="color:black;background-color:#66ff66">INDEX</b> test_pgsphere_idx ON test_pgsphere USING gist(point);

-- Conesearch on static data
SELECT count(*) FROM test_q3c WHERE q3c_radial_query(ra, dec, <b style="color:black;background-color:#6666ff">0</b>, <b style="color:black;background-color:#6666ff">0</b>, 10);
SELECT count(*) FROM test_pgsphere where point @ scircle '<(<b style="color:black;background-color:#6666ff">0</b>, <b style="color:black;background-color:#6666ff">0</b>), 10d>';

-- Live update / preparation 
SELECT generate_series(1,1000000) AS id, NULL::float AS ra, NULL::float AS dec INTO running_q3c;
CREATE <b style="color:black;background-color:#66ff66">INDEX</b> 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 <b style="color:black;background-color:#66ff66">INDEX</b> running_pgsphere_idx ON running_pgsphere USING gist(point);

-- Live update - run
UPDATE running_q3c SET (ra, dec) = (random()*360, (random()-<b style="color:black;background-color:#6666ff">0</b>.5)*180);
UPDATE running_pgsphere SET point = spoint(random()*2*3.1415926, (random()-<b style="color:black;background-color:#6666ff">0</b>.5)*3.1415926);

</code>

<code>
q3ctest=# \timing
Timing is on.
q3ctest=# -- Static data
q3ctest=# SELECT generate_series(1,1000000) AS id, random()*360 AS ra, (random()-<b style="color:black;background-color:#6666ff">0</b>.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()-<b style="color:black;background-color:#6666ff">0</b>.5)*3.1415926) AS point INTO test_pgsphere;
SELECT 1000000
Time: 8886.958 ms
q3ctest=#
q3ctest=# -- <b style="color:black;background-color:#66ff66">Index</b> on static data
q3ctest=# CREATE <b style="color:black;background-color:#66ff66">INDEX</b> test_q3c_idx ON test_q3c (q3c_ang2ipix(ra, dec));
CREATE <b style="color:black;background-color:#66ff66">INDEX</b>
Time: 11210.999 ms
q3ctest=# CREATE <b style="color:black;background-color:#66ff66">INDEX</b> test_pgsphere_idx ON test_pgsphere USING gist(point);
CREATE <b style="color:black;background-color:#66ff66">INDEX</b>
Time: 192927.871 ms
q3ctest=#
q3ctest=# -- Conesearch on static data
q3ctest=# SELECT count(*) FROM test_q3c WHERE q3c_radial_query(ra, dec, <b style="color:black;background-color:#6666ff">0</b>, <b style="color:black;background-color:#6666ff">0</b>, 10);
 count
-------
  4879
(1 row)

Time: 943.568 ms
q3ctest=# SELECT count(*) FROM test_pgsphere where point @ scircle '<(<b style="color:black;background-color:#6666ff">0</b>, <b style="color:black;background-color:#6666ff">0</b>), 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 <b style="color:black;background-color:#66ff66">INDEX</b> running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));
CREATE <b style="color:black;background-color:#66ff66">INDEX</b>
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 <b style="color:black;background-color:#66ff66">INDEX</b> running_pgsphere_idx ON running_pgsphere USING gist(point);
CREATE <b style="color:black;background-color:#66ff66">INDEX</b>
Time: 25023.983 ms
q3ctest=#
q3ctest=# -- Live update - run
q3ctest=# UPDATE running_q3c SET (ra, dec) = (random()*360, (random()-<b style="color:black;background-color:#6666ff">0</b>.5)*180);
UPDATE 1000000
Time: 58032.557 ms
q3ctest=# UPDATE running_pgsphere SET point = spoint(random()*2*3.1415926, (random()-<b style="color:black;background-color:#6666ff">0</b>.5)*3.1415926);
UPDATE 1000000
Time: 249849.524 ms
</code>
q3c.1279781064.txt.gz · Last modified: 2010/07/22 10:44 by karpov
Back to top