Äîêóìåíò âçÿò èç êýøà ïîèñêîâîé ìàøèíû. Àäðåñ
îðèãèíàëüíîãî äîêóìåíòà
: http://vo.astronet.ru/wiki/q3c?rev=1279733626&do=diff
Äàòà èçìåíåíèÿ: Unknown Äàòà èíäåêñèðîâàíèÿ: Wed Apr 13 06:10:09 2016 Êîäèðîâêà: Ïîèñêîâûå ñëîâà: ð ð ð ð ñ ð ð ð ï ï ñ ð ð ñ ð ð ñ ð ñ ñ ð ð ñ ð ð ð ð |
This shows you the differences between the selected revision and the current version of the page.
q3c 2010/07/21 21:33 | q3c 2010/07/24 16:34 current | ||
---|---|---|---|
Line 8: | Line 8: | ||
<code> | <code> | ||
- | q3ctest=# \timingá | + | \timing oná |
- | Timing is on.á | + | DROP TABLE running_q3c;á |
- | q3ctest=# -- Static dataá | + | DROP TABLE running_pgsphere;á |
- | q3ctest=# SELECT generate_series(1,1000000) AS id, random()*360 AS ra, (random()-0.5)*180 AS dec INTO test_q3c;á | + | á |
- | SELECT 1000000á | + | CREATE TABLE running_q3c (id INT, ra FLOAT, dec FLOAT);á |
- | Time: 7324.102 msá | + | CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec));á |
- | q3ctest=# SELECT generate_series(1,1000000) AS id, spoint(random()*2*3.1415926, (random()-0.5)*3.1415926) AS point INTO test_pgsphere;á | + | CREATE TABLE running_pgsphere (id INT, point spoint);á |
- | SELECT 1000000á | + | CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point);á |
- | Time: 8886.958 ms | + | á |
+ | -- 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;á | ||
+ | </code>á | ||
+ | á | ||
+ | <code>á | ||
+ | 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=# | ||
- | q3ctest=# -- Index on static dataá | + | q3ctest=# CREATE TABLE running_q3c (id INT, ra FLOAT, dec FLOAT);á |
- | q3ctest=# CREATE INDEX test_q3c_idx ON test_q3c (q3c_ang2ipix(ra, dec)); | + | CREATE TABLEá |
+ | Time: 81.882 msá | ||
+ | q3ctest=# CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec)); | ||
CREATE INDEX | CREATE INDEX | ||
- | Time: 11210.999 msá | + | Time: 62.291 msá |
- | q3ctest=# CREATE INDEX test_pgsphere_idx ON test_pgsphere USING gist(point); | + | 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 | CREATE INDEX | ||
- | Time: 192927.871 ms | + | Time: 72.616 ms |
q3ctest=# | q3ctest=# | ||
- | q3ctest=# -- Conesearch on static dataá | + | q3ctest=# -- Streaming data with index updateá |
- | q3ctest=# SELECT count(*) FROM test_q3c WHERE q3c_radial_query(ra, dec, 0, 0, 10);á | + | q3ctest=# INSERT INTO running_q3c (id, ra, dec) (SELECT generate_series(1,100000), random()*360, (random()-0.5)*180);á |
- | countá | + | INSERT 0 100000á |
- | -------á | + | Time: 3811.922 msá |
- | 4879á | + | q3ctest=# INSERT INTO running_pgsphere (id, point) (SELECT generate_series(1,100000), spoint(random()*2*pi(), (random()-0.5)*pi()));á |
- | (1 row)á | + | INSERT 0 100000á |
- | á | + | Time: 18366.058 msá |
- | Time: 943.568 msá | + | q3ctest=# -- Once moreá |
- | q3ctest=# SELECT count(*) FROM test_pgsphere where point @ scircle '<(0, 0), 10d>';á | + | q3ctest=# INSERT INTO running_pgsphere (id, point) (SELECT generate_series(1,100000), spoint(random()*2*pi(), (random()-0.5)*pi()));á |
- | countá | + | INSERT 0 100000á |
- | -------á | + | Time: 21094.975 msá |
- | 4909á | + | q3ctest=# INSERT INTO running_q3c (id, ra, dec) (SELECT generate_series(1,100000), random()*360, (random()-0.5)*180);á |
- | (1 row)á | + | INSERT 0 100000á |
- | á | + | Time: 3794.624 ms |
- | Time: 178.119 ms | + | |
q3ctest=# | q3ctest=# | ||
- | q3ctest=# -- Live update / preparationá | + | q3ctest=# DROP INDEX running_q3c_idx;á |
- | q3ctest=# SELECT generate_series(1,1000000) AS id, NULL::float AS ra, NULL::float AS dec INTO running_q3c;á | + | DROP INDEXá |
- | SELECT 1000000á | + | Time: 11.024 msá |
- | Time: 5435.335 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)); | q3ctest=# CREATE INDEX running_q3c_idx ON running_q3c (q3c_ang2ipix(ra, dec)); | ||
CREATE INDEX | CREATE INDEX | ||
- | Time: 3460.449 msá | + | Time: 1623.090 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); | q3ctest=# CREATE INDEX running_pgsphere_idx ON running_pgsphere USING gist(point); | ||
CREATE INDEX | CREATE INDEX | ||
- | Time: 25023.983 ms | + | 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=# | ||
- | q3ctest=# -- Live update - runá | + | q3ctest=# -- Once moreá |
- | q3ctest=# UPDATE running_q3c SET (ra, dec) = (random()*360, (random()-0.5)*180);á | + | 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;á |
- | UPDATE 1000000á | + | SELECT 1000á |
- | Time: 58032.557 msá | + | Time: 1058.741 msá |
- | q3ctest=# UPDATE running_pgsphere SET point = spoint(random()*2*3.1415926, (random()-0.5)*3.1415926);á | + | 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;á |
- | UPDATE 1000000á | + | SELECT 1000á |
- | Time: 249849.524 ms | + | Time: 8666.836 ms |
</code> | </code> |