OBSOLETE FEATURE: indexed objects for pgsphere

[kimakan]

I guess that at some point there were plans to use the extra column of type spoint(ra, dec) which is indexed and then, it can be used instead of ra, dec. I'm not quiet sure whether the implementation was completed. The code in question is

daiquiri/daiquiri/query/process.py

Lines 178 to 185 in 5ae2a14

	# first run to replace with get_indexed_objects
	processor.set_query(query)
	processor.process_query(indexed_objects=get_indexed_objects(), replace_schema_name={
	'TAP_SCHEMA': settings.TAP_SCHEMA,
	'tap_schema': settings.TAP_SCHEMA,
	'TAP_UPLOAD': settings.TAP_UPLOAD,
	'tap_upload': settings.TAP_UPLOAD,
	})

daiquiri/daiquiri/query/utils.py

Lines 132 to 148 in 5ae2a14

	def get_indexed_objects():
	indexed_objects = {}
	for column in Column.objects.exclude(index_for=''):
	# TODO implement xtype 'spoint' properly
	#if column.datatype not in indexed_objects:
	# indexed_objects[column.datatype] = [column.indexed_columns]
	#else:
	# indexed_objects[column.datatype].append(column.indexed_columns)
	if 'spoint' not in indexed_objects:
	indexed_objects['spoint'] = [column.indexed_columns]
	else:
	indexed_objects['spoint'].append(column.indexed_columns)
	return indexed_objects

IMHO, the feature is obsolete because the indexes can be created directly for the spoint() expression. Meaning that instead of creating a new column pos as spoint(ra, dec), creating an index for it and then trying in some hacky way to replace spoint(ra,dec) with the indexed column, we just create an index as

Create index schema_table_spoint_ra_dec on schema.table  using gist (spoint(RADIANS(ra), RADIANS(dec)));

No need to create an extra column in the database and no need to connect ra/dec with the spoint(ra,dec) in the translated query.

Removing this feature would simplify daiquiri and handling of the database.

[jochenklar]

Maybe we just did not know enough about databases back then. Did you test if your solution is as fast as the one with the extra column?

[kimakan]

Yes, we have already tested (and still using) the index spoint(RADIANS(ra), RADIANS(dec)) for gaia DR3. In the case of the cone search, the new solution is even faster (~3x).