Now we fixed this using `q3c`. Could you briefly summarize what you did with the SQL commands, please? monodera

Also, Masayuki Tanaka mentioned that we might need to think about distributed querying using such as Citus when targetDB expands in future.

q3c was installed by Furusawa-san as an extension of PostgreSQL. After the installation, I logged into the current targetdb (currently targetdb_comm2022may database) and executed the following SQL commands.

CREATE EXTENSION q3c;
CREATE INDEX ON sky (q3c_ang2ipix(ra,dec));
CLUSTER sky_q3c_ang2ipix_idx ON sky;
ANALYZE sky;

There are various functions in the q3c extension, but at this point only q3c_radial_query() which simply returns true if (ra, dec) is in a search radius. An example query is the following.

SELECT * FROM {tablename} WHERE q3c_radial_query(ra, dec, {ra}, {dec}, {search_radius})

Documentation can be found at the following GitHub repository.

https://github.com/segasai/q3c
 

BTW, the q3c indexing is only done in the sky table.

FYI, Murata-san commented that the HSC SSP database uses the earthdistance extension in postgresql for indexing. Coord column is used as index.

According to monodera efficient query was achieved by indexing with q4c. If other method needs to be used (see the above comment), it will be handled in another ticket.