Questions tagged [gist-index]
35 questions
51
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
- 785
- 1
- 6
- 10
12
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
- 535
- 1
- 6
- 16
9
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
- 281
- 1
- 3
- 12
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
- 982
- 3
- 11
- 23
6
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
- 417
- 1
- 4
- 10
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
- 3
- 6
4
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
- 43
- 3
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…
FallenAngel
- 439
- 1
- 5
- 13
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
- 836
- 7
- 22
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
3
votes
2 answers
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 data that shows the different planning. The…
Alex Camargo
- 53
- 5
2
votes
1 answer
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
- 239
- 1
- 3
- 12