Questions tagged [gist-index]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
55 views

Postgresql strange behavior with non-ascii characters with triagram index present

I see some strange behavior when using either gin_trgm_ops or gist_trgm_ops indexes. There seems to be quite a difference in plans when using say ILIKE or ~ and search for ascii phrases vs multi-byte ...
Pawel Zieminski's user avatar
0 votes
1 answer
69 views

Why does my GiST index not trigger a conflict? [duplicate]

I have a table with user subscriptions in my Postgres 13 DB. I added a GiST index to disallow overlapping time ranges for the same user: CREATE INDEX user_sub_exclusion_index ON public.user_sub USING ...
Dolphin's user avatar
  • 775
1 vote
0 answers
94 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 ...
Avocado's user avatar
  • 223
0 votes
1 answer
427 views

Optimizing geolocation query in Postgres with earth_box and earth_distance

I'm trying to optimize my geolocation query for a table of addresses of ~862k rows within a search radius using earth_box. My first initial query is not too terrible: explain analyze SELECT id ...
blin's user avatar
  • 3
0 votes
1 answer
317 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, ...
Cooper Wolfe's user avatar
1 vote
0 answers
317 views

Why does a GIST index on a cube column in PostgreSQL actually make K-Nearest Neighbor (KNN) ORDER BY queries worse?

Adding a GIST index actually seems to make K-Nearest Neighbor (KNN) ORDER BY queries on cube columns worse in PostgreSQL. Why would that be, and what can be done about it? Here's what I mean. In a ...
David Ventimiglia's user avatar
2 votes
3 answers
428 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 ...
Boodoo's user avatar
  • 45
3 votes
0 answers
336 views

Ordering of columns in a GIST index

I am modelling an application where events for factories can be registered. There are usually a small amount of factories (around 500), but each factory can register a large set of events (millions). ...
ItIsJustMe's user avatar
6 votes
1 answer
2k views

Why is a GiST index used for filtering on non-leading column?

I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query....
Kejlo's user avatar
  • 63
0 votes
1 answer
126 views

Composite multicolumn index for geopoint range and numeric range query

I am building an app where the server needs to select rows based on some criteria/filters. One of them is the location of the user and the radius at which the user want's to see posts and other ...
Najib's user avatar
  • 221
0 votes
3 answers
364 views

slow index creation with gist and gist__int_ops

I am seeing performance issues with frequently updated / inserts table, and one of my hypothesis is the gin index I have on int array. The gin index was added since we have query of "X exists in ...
Yosi's user avatar
  • 175
1 vote
1 answer
4k views

How to increase index row maximum size in Postgresql?

I want to perform KNN on 512-dimensional vector using the solution suggested here. I couldn't create table with 512-d cube array. Solved by changing the CUBE_MAX_DIM in the source code. Couldn't ...
Alibi Yeslambek's user avatar
0 votes
1 answer
657 views

Create gist index on cube array with 512 dimensions

I want to perform nearest neighbors search on db with 5000 entries of cube array with 512 dimensions. I referred to this answer and used docker image of postgresql with cube array limit set to 2048 ...
Alibi Yeslambek's user avatar
3 votes
4 answers
4k views

Postgres LIKE query using a GiST index is just as slow as a full scan

What I have is a very simple database that stores paths, extensions and names of files from UNC shares. For testing, I inserted about 1,5 mio rows and the below query uses a GiST index as you see, but ...
Harry's user avatar
  • 31
3 votes
2 answers
3k views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
Gajus's user avatar
  • 1,175
2 votes
1 answer
271 views

PostgreSQL: Issue with a corrupted GiST index on ll_to_earth

I'm facing something that seems like an index corruption issue on a table that stores about 120k entries. Each entry has a latlon and this latlon is indexed using the ll_to_earth GiST implementation: ...
neeh's user avatar
  • 121
2 votes
1 answer
242 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, ...
Alex Camargo's user avatar
3 votes
2 answers
134 views

PostgreSQL GiST compress skeleton when no compression required

I'm trying to implement my GiST index, where storage type is the same as column type (bytea). They are even going to have same length because these are bitarrays and unions are just disjunctions of ...
Raven's user avatar
  • 133
1 vote
2 answers
299 views

Optimizing a "categorized" search table using trigrams

I've been reading through this excellent answer to understand how pg_trgm works a bit, but I'm still unclear on the most efficient way of solving this query (efficient in terms of speed of search): I ...
brad's user avatar
  • 187
0 votes
1 answer
981 views

Queries using GIN index on f_unaccent() seem slow?

I have almost 20M rows in one table that contains product, including their names. I wish to search fulltext and fast by the name so I created this index: CREATE INDEX ...
Michal's user avatar
  • 3
2 votes
1 answer
676 views

Exclusion constraint on ENUM errors: data type dow has no default operator class for access method "gist"

Following in the style of this, I'm trying to create an exclusion constraint over an ENUM, CREATE TYPE dow AS ENUM ( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday' );...
Evan Carroll's user avatar
  • 62.7k
3 votes
1 answer
780 views

Exclusion constraint generates: ERROR: operator &&(anyrange,anyrange) is not a member of operator family "range_ops"

How come when I try to create an exclusion constraint with GIST, CREATE TABLE foo ( a tsrange, EXCLUDE (a WITH &&) ); ERROR: operator &&(anyrange,anyrange) is not a member of ...
Evan Carroll's user avatar
  • 62.7k
3 votes
2 answers
1k views

PostgreSQL planner choosing btree or gist index for few result rows

I have a scenario where a search on a range with the same value on both sides is using a plan with an index that is not the composite index I expected. After some work, I was able to generate a sample ...
Alex Camargo's user avatar
0 votes
1 answer
823 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 ...
yurtesen's user avatar
  • 227
4 votes
2 answers
3k views

ERROR: data type text[] has no default operator class for access method "gist"

Whenever I try to try to create a GIST index on text[], I get the above error. CREATE TABLE bar(f) AS VALUES (ARRAY['foo','bar','baz']); CREATE INDEX ON bar USING gist(f); ERROR: data type text[]...
Evan Carroll's user avatar
  • 62.7k
2 votes
1 answer
212 views

Ranges over domains do not trigger implicit casting

As far as I was able to find out, it is currently not possible in PostgreSQL v9.5 (and above?) to obtain implicit casting with custom range types. To illustrate, when I define the following demo setup:...
John Frazer's user avatar
5 votes
1 answer
6k views

Best index for jsonb in Postgres

We have a table containing around 500k rows. The database table is supposed to grow to million of records. This is how the table looks like: CREATE TABLE public.influencers ( id integer NOT NULL ...
borjagvo's user avatar
  • 205
7 votes
2 answers
8k views

How can I speed-up my query on geo-location processes

I have a table that contains 10,301,390 GPS records, cities, countries and IP address blocks. I have user's current location with latitude and longitude. I created this query: SELECT *, point(45....
xangr's user avatar
  • 437
2 votes
2 answers
117 views

PostgreSQL GiST for compressed entry type

I am using PostgreSQL 9.5 and I'm trying to understand how to implement a GiST index where I have a representation that is a lossily compressed version of the indexed type. For instance, say I have ...
beldaz's user avatar
  • 1,680
4 votes
1 answer
4k views

PostgreSQL - Datetime ranges overlap

I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table. The current query looks like this: ...
Anna's user avatar
  • 41
2 votes
0 answers
84 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-...
TristanEc's user avatar
2 votes
1 answer
472 views

How to determine if postgres database contains GiST indexes and what type?

Is there a way to easily check if a PostgreSQL database has any GiST indexes and of what type they are?
user972276's user avatar
2 votes
1 answer
233 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 ...
Gandalf StormCrow's user avatar
8 votes
2 answers
4k views

PostgreSQL, integer arrays, index for equality

I have a huge list of integer arrays (300,000,000 records) stored in Postgres 9.2 DB. I want to efficiently search these records for an exact match (equality only). I have heard of the intarray module ...
Alexandros's user avatar
  • 1,012
15 votes
1 answer
8k 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 || ')' ), ...
Gandalf StormCrow's user avatar
11 votes
1 answer
6k views

2 B-tree indices OR 1 GiST index on tsrange -- which will perform better?

I have a table which is storing reservation data using the columns starts_at & ends_at Whenever I'm querying the table to find overlapping reservations, I have an option of using one of the ...
Saurabh Nanda's user avatar
0 votes
1 answer
213 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 ...
Steve Bennett's user avatar
3 votes
1 answer
1k views

Index method for very few updates and many inserts

I am using Postgresql 9.1 with *pg_trgm* extension. I need to create an index on a text-based field. I do not need full-text searches, I use ILIKE queries to make my searches. I will use pg_trgm but ...
Mp0int's user avatar
  • 459
60 votes
2 answers
39k views

PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

In PostgreSQL 9.2.3 I am trying to create this simplified table: CREATE TABLE test ( user_id INTEGER, startend TSTZRANGE, EXCLUDE USING gist (user_id WITH =, startend WITH &&) ); ...
Ian Timothy's user avatar
2 votes
1 answer
480 views

Does spgist extension in PostgreSQL 9.2.3 support float or int array?

SP-GiST is an abbreviation for space-partitioned GiST in PostgreSQL 9.2.3. It can be used to find the similar tuple by creating the sp-gist index on the column. e.g: create index pt_spgist_idx on ...
zhfkt's user avatar
  • 121