Документ взят из кэша поисковой машины. Адрес
оригинального документа
: http://vo.astronet.ru/wiki/q3c?rev=1279781064
Дата изменения: Unknown Дата индексирования: Sun Apr 10 02:57:38 2016 Кодировка: Поисковые слова: п п п п п п п п п п п п п п п п п п п п п п п п п п п п п п п |
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 msBack to top