3 ms·

Congratulations! Can we expect support for gist, gin, spgist, and brin indexes sometime in the near future? Based on the source code, it appears that they are

by pella 3y ago

Congratulations! Can we expect support for gist, gin, spgist, and brin indexes sometime in the near future? Based on the source code, it appears that they are not supported: https://github.com/hydradatabase/hydra/blob/96056312e7c0f4135e31ce5380f4f7b4cfb7b42f/columnar/src/backend/columnar/README.md?plain=1#L190 https://github.com/hydradatabase/hydra/blob/96056312e7c0f413... "... Columnar supports `btree` and `hash `indexes (and the constraints requiring them) but does not support `gist`, `gin`, `spgist` and `brin` indexes."

nerdponx 3y ago
Do different kinds of indexes work better for columnar storage? Or is it the same principles for both?
- hodgesrm 3y ago
  Difference principles of indexing, as least based on my experience with ClickHouse. * Column-based stores have really fast scans due to compression and vectorization, so you'll generally always read down the column. The way to speed it up is to have "skip indexes" that allow you to skip blocks, e.g., don't even bother to read/decompress them. * Commonly used indexes need to be very sparse, so they fit in memory even when tables run to hundreds of billions of rows. * Finally highly compressed columns can be used as indexes to filter data rapidly. ClickHouse calls this PREWHERE processing. Edit: clarify skip indexes
- pella 3y ago
  We need a spatial index for spatial (columnar) data! - https://www.crunchydata.com/blog/the-many-spatial-indexes-of-postgis https://www.crunchydata.com/blog/the-many-spatial-indexes-of... - http://postgis.net/workshops/postgis-intro/indexing.html http://postgis.net/workshops/postgis-intro/indexing.html - Spatial indexes for OSM in PostGIS (PDF) : https://pretalx.com/media/sotm2019/submissions/CAD93S/resources/Kunde_spatial_indexes_for_osm_in_postgis.pdf https://pretalx.com/media/sotm2019/submissions/CAD93S/resour...

by pella 3y ago

nerdponx 3y ago

Do different kinds of indexes work better for columnar storage? Or is it the same principles for both?

hodgesrm 3y ago
Difference principles of indexing, as least based on my experience with ClickHouse. * Column-based stores have really fast scans due to compression and vectorization, so you'll generally always read down the column. The way to speed it up is to have "skip indexes" that allow you to skip blocks, e.g., don't even bother to read/decompress them. * Commonly used indexes need to be very sparse, so they fit in memory even when tables run to hundreds of billions of rows. * Finally highly compressed columns can be used as indexes to filter data rapidly. ClickHouse calls this PREWHERE processing. Edit: clarify skip indexes
pella 3y ago
We need a spatial index for spatial (columnar) data! - https://www.crunchydata.com/blog/the-many-spatial-indexes-of-postgis https://www.crunchydata.com/blog/the-many-spatial-indexes-of... - http://postgis.net/workshops/postgis-intro/indexing.html http://postgis.net/workshops/postgis-intro/indexing.html - Spatial indexes for OSM in PostGIS (PDF) : https://pretalx.com/media/sotm2019/submissions/CAD93S/resources/Kunde_spatial_indexes_for_osm_in_postgis.pdf https://pretalx.com/media/sotm2019/submissions/CAD93S/resour...