search

segasai / q3c

PostgreSQL extension for spatial indexing on a sphere
GNU General Public License v2.0
76 stars 27 forks source link

pg_repack q3c index #24

Closed albireox closed 4 years ago

albireox commented 4 years ago

I'm dealing with some huge tables for which clustering after creating the q3c index can take several days, during which the tables are locked (I cannot even create indexes for other tables). A non-locking alternative seems to be pg_repack. Do you have experience with it and know if it will work with q3c indixes?

segasai commented 4 years ago

I personally never use cluster, as it is too slow. I always use create table as select * from tab order by q3c_ang2ipix(ra,dec) instead. It is faster . And then one can always substitute the original table. Regarding pg_repack, I'd think it should work fine, as q3c index is just a regular functional index..

albireox commented 4 years ago

That's very useful, will try it. Thanks!

albireox commented 4 years ago

One additional question. After you've created the the new table sorted by ipix, do you then add an index to that table on q3c_ang2ipix or with the sorting that's not needed?

segasai commented 4 years ago

You still need to create an index on q3c_ang2ipix() after sorting.