All Questions
Tagged with gist-index postgis
9 questions
1
vote
0
answers
126
views
Curious results from pg_stat_user_indexes
PostgreSQL 12.8, PostGIS 3.1.4
Running select * from pg_stat_user_indexes, there are some rows that have 0 for idx_scan, and a >0 result for idx_tup_read.
The indexes that match this condition are ...
- 233
0
votes
1
answer
488
views
I have a GIST index and a regular index, one intended for filtering, one intended for sorting, but Postgres WILL NOT use both
I have a table with three columns: two can be used to filter the table, and the last can be used to sort it:
create table thing(
id serial primary key,
location geometry(Point, 4326) not null,
...
2
votes
3
answers
767
views
Is a GIST index on a geometry point useful to speed up a spatial query?
I have multiple tables with point geometries and a GiST index eating many gigabits of space.
From what I understand about GiST indexes on polygons or polylines, it will store the bounding box and ...
- 45
2
votes
1
answer
258
views
Postgresql bad planning with multicolumn gist and search with empty results
I'm having an issue with a multicolumn index (integer, geom), where geom are PostGIS points, and the integer is a point aggregator with business meaning. I do searches by boxes in the geom column, ...
- 53
0
votes
1
answer
920
views
Why PostGIS GIST index is not used? [closed]
I am using PostGIS 2.2.1 with PostgreSQL 9.5.10. I have a table with a geography field for coordinates and I am making a simple bounding box query. The table has 1029 rows. EXPLAIN says it does a ...
- 237
2
votes
0
answers
85
views
PostgreSQL: Improve system performance, KNN modification with GiST Index
In my PostgreSQL 9.5beta2, Postgis 2.2.0 compiled by Visual C++ build 1800, 64-bit I am running a Nearest-Neighbor-Query with the additional complication of unique assignment. I am running this on i5-...
- 21
2
votes
1
answer
242
views
PostGis getting rid of the lat long from table
This question is related to this question :
Order by distance
Now my tables have changed a bit. Table cafes now doesn't have latitude, longitude columns instead there are table addresses which ...
15
votes
1
answer
9k
views
Order by distance
If I have a query returning nearby cafes:
SELECT * FROM cafes c WHERE (
ST_DWithin(
ST_GeographyFromText(
'SRID=4326;POINT(' || c.longitude || ' ' || c.latitude || ')'
),
...
0
votes
1
answer
216
views
Best read performance when every query is known, and the data doesn't change
I'm trying to maximise performance on an underpowered PostGIS server - 1 core, 4 CPUs. The data (OpenStreetMap) will be infrequently (monthly?) loaded, and never changes otherwise. Each of the 30 or ...
- 141