Документ взят из кэша поисковой машины. Адрес
оригинального документа
: http://vo.astronet.ru/wiki/q3c
Дата изменения: Unknown Дата индексирования: Sat Apr 9 22:34:50 2016 Кодировка: Поисковые слова: earth's atmosphere |
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 msBack to top