3 ms·

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not just dealing with points.

manigandham 7y ago
So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes. If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.
- anarazel 7y ago
  You need different index structures. Yes, it matters. Hard to believe that spatial queries over geometries more complex than points will meaningfully benefit from "vectorized executions on columnstores".
  - manigandham 7y ago
    Do you have an example query? If you're not using geospatial features, then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?
    - anarazel 7y ago
      > Do you have an example query? I mean, just about any geospatial query? E.g. finding all polygons in a table that overlap with one specified in a query. https://postgis.net/docs/ST_Overlaps.html https://postgis.net/docs/ST_Overlaps.html > If you're not using geospatial features, Why are you talking about not using geospatial features? I was just responding to your > Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. statement. Which I still think is not a meaningful alternative. And then subsequently to your statement about vectorized columnstores. I'm not all saying that only postgres has geospatial indexing support (although postgis is pretty good) - that would be ridiculous. > then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)? I do think that postgres' indexing support (gist, gin in particular) makes it easier to add support for indexing types of data that are harder to index in many other databases. But it's nothing that can't be beat.
      - manigandham 7y ago
        The point is that without geospatial features, you can still use other strategies like lat/long or geohash and they will all have the same performance. The logic (built-in geospatial or hand-written sql) is irrelevant to the performance comparison and GP's post that Postgres was the only one that could query quickly. Do you disagree with that? If not then I'm not sure what your argument is.

> Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. Not that trivial if you're not just dealing with points.

manigandham 7y ago

So you have more complicated SQL then, but it doesn't make a difference between DBs selecting from a 100M table with indexes. If it ends up being a full table scan then PG only recently got query compilation and parallel execution so at best it would be even with, but not better than, MSSQL/Oracle that can do vectorized executions on columnstores.

anarazel 7y ago
You need different index structures. Yes, it matters. Hard to believe that spatial queries over geometries more complex than points will meaningfully benefit from "vectorized executions on columnstores".
- manigandham 7y ago
  Do you have an example query? If you're not using geospatial features, then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)?
  - anarazel 7y ago
    > Do you have an example query? I mean, just about any geospatial query? E.g. finding all polygons in a table that overlap with one specified in a query. https://postgis.net/docs/ST_Overlaps.html https://postgis.net/docs/ST_Overlaps.html > If you're not using geospatial features, Why are you talking about not using geospatial features? I was just responding to your > Even if they didn't, there are plenty of algorithms based on lat/lon which uses numeric/float data types with simple indexes. statement. Which I still think is not a meaningful alternative. And then subsequently to your statement about vectorized columnstores. I'm not all saying that only postgres has geospatial indexing support (although postgis is pretty good) - that would be ridiculous. > then what indexes does PG offer that the others can't match (either directly with indexes or with better scan/query performance)? I do think that postgres' indexing support (gist, gin in particular) makes it easier to add support for indexing types of data that are harder to index in many other databases. But it's nothing that can't be beat.
    - manigandham 7y ago
      The point is that without geospatial features, you can still use other strategies like lat/long or geohash and they will all have the same performance. The logic (built-in geospatial or hand-written sql) is irrelevant to the performance comparison and GP's post that Postgres was the only one that could query quickly. Do you disagree with that? If not then I'm not sure what your argument is.