Questions tagged [gist-index]
41 questions
62
votes
2 answers
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 &&)
);
But I get this error:
ERROR: data type integer has no…
Ian Timothy
- 905
- 1
- 7
- 10
15
votes
1 answer
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 || ')'
),
ST_GeographyFromText('SRID=4326;POINT(-76.000000…
Gandalf StormCrow
- 615
- 1
- 8
- 17
12
votes
1 answer
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 following queries:
SELECT * FROM reservations
WHERE…
Saurabh Nanda
- 333
- 1
- 4
- 16
8
votes
2 answers
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 and the corresponding gist-gin indexes. I would…
Alexandros
- 1,022
- 4
- 12
- 23
7
votes
2 answers
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.1013021, 46.3021011) <@> point(latitude, longitude)…
xangr
- 457
- 1
- 5
- 10
6
votes
1 answer
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. Why is that? Is this a special feature of GiST…
Kejlo
- 63
- 4
5
votes
1 answer
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 DEFAULT nextval('influencers_id_seq'::regclass),
…
borjagvo
- 205
- 4
- 6
4
votes
2 answers
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[] has no default operator class for access method…
Evan Carroll
- 65,432
- 50
- 254
- 507
4
votes
1 answer
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:
select br.duration from booking, (
select…
Anna
- 41
- 1
- 2
3
votes
1 answer
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 do not have much experience with gin and gist…
Mp0int
- 459
- 1
- 6
- 14
3
votes
3 answers
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 compute a first calculation on the bounding box…
Boodoo
- 65
- 8
3
votes
0 answers
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). It's impossible that two events overlap for the…
ItIsJustMe
- 33
- 3
3
votes
4 answers
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 still it takes 5 seconds to return. Expected…
Harry
- 31
- 1
- 6
3
votes
2 answers
Containment check tstzrange @> timestamptz not using btree or gist index
Schema:
Column | Type
----------------------+--------------------------
id | integer
event_id | integer
started_at | timestamp with…
Gajus
- 1,334
- 16
- 29
3
votes
2 answers
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 all arrays in set (in other words, bitwise OR of…
Raven
- 133
- 4