All Questions
Tagged with gist-index postgresql
39 questions
1
vote
1
answer
184
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 ...
- 125
0
votes
1
answer
92
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 ...
- 825
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
651
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
...
- 3
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,
...
1
vote
0
answers
345
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 ...
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
3
votes
0
answers
405
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). ...
- 33
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....
- 63
0
votes
1
answer
161
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 ...
- 231
0
votes
3
answers
452
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 ...
- 175
1
vote
1
answer
5k
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 ...
0
votes
1
answer
682
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 ...
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 ...
- 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 ...
- 1,274
2
votes
1
answer
315
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:
...
- 121
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
3
votes
2
answers
144
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 ...
- 133
1
vote
2
answers
330
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 ...
- 187
0
votes
1
answer
1k
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 ...
- 3
2
votes
1
answer
765
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'
);...
- 64.6k
3
votes
1
answer
910
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 ...
- 64.6k
3
votes
2
answers
2k
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 ...
- 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
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[]...
- 64.6k
2
votes
1
answer
238
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:...
- 317
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 ...
- 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....
- 447
2
votes
2
answers
127
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 ...
- 1,730
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:
...
- 41
2
votes
1
answer
518
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?
- 309
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 ...
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 ...
- 1,012
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 || ')'
),
...
12
votes
1
answer
7k
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 ...
- 333
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
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 ...
- 459
62
votes
2
answers
42k
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 &&)
);
...
- 905
2
votes
1
answer
488
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 ...
- 121