28 questions
0
votes
2
answers
151
views
Slow query with like on Postgres 14.6 [closed]
I have a table that contains ~ 100.000.000 records. In this table, there's a varchar(30) column that contains the license plates of cars. I'm joining this table with another table that contains the ...
- 13
0
votes
1
answer
1k
views
GIST index or other alternative for indexing JSONB columns in Postgres
Context:
Postgres 15
table hundreds of millions or maybe billions of rows, the tables contain an id colummn and a JSONB column
no other constraints (FKs)
selecting from the table is usually/often ...
- 3,436
0
votes
2
answers
319
views
How to make a composite index in Postgres including a POINT?
With this table:
CREATE TABLE bins (
id SERIAL PRIMARY KEY,
start TIMESTAMP NOT NULL,
bits BIT(16) NOT NULL,
topleft POINT, -- (x,y) in web mercator projection
count INTEGER
);
...
- 45.2k
1
vote
1
answer
195
views
Cannot use siglen parameter while creating index gist_trgm_ops in PostgreSQL 11.19 (Azure flexible DB)
When trying to create index on remote Azure server (PostgreSQL 11.19 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit) create index test on "Table" using ...
- 21
2
votes
1
answer
4k
views
Postgres function raising error when calling from Python script but running fine in PgAdmin
I have a postgres function called 'medrealize()' inside which i am creating temp table and creating index for temp tables using below lines
CREATE INDEX SQLOPS_RefICD_ICD10_idx ON t$...
- 853
0
votes
1
answer
540
views
The created gist index is not considered during the operation for 50m rows in a table
I am trying to write a new column based on the intersection of two polygon features which have 50m rows in one table, I have created the "gist" index before running them but it takes forever ...
- 91
0
votes
0
answers
106
views
Poor performance of postgresql KNN queries
There are two DB machines identical in structure and configurations with the characteristics as below. The databases are also identical in structure and contents. The performance of almost 99% of ...
1
vote
1
answer
356
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 ...
- 229
3
votes
1
answer
926
views
PostgreSQL exclusion constraints in a bitemporal setting?
I am currently working with a bitemporal application, which stores data entries using 4 timestamps:
Valid_from, Valid_to
Registration_from, Registration_to
The first two state when the given entry is ...
- 733
1
vote
0
answers
46
views
Is it possible to call function in exclude contrains to return only specific column
I'm trying to implement exclude constraint and I have written the function which returns more than one separate parameters (types: character varying). What I want is to call this function twice, but ...
- 352
1
vote
1
answer
1k
views
GIST index creation too slow on PostgreSQL
I have a database in PostgreSQL with the following structure:
Column | Type | Collation | Nullable | Default
-------------+-----------------...
- 1,971
2
votes
1
answer
186
views
Is the relationship between index tuple in GiST index and user table row many to one or one to one?
In a regular b-tree index, the leaf node contains a key and a pointer to the heap tuple (user table row), which signifies that in b-tree, the relationship between index tuple and user table row is one-...
- 691
4
votes
0
answers
360
views
Postgres cube type distance vector index slower than seq scan
With a 128 dimension column and a distance query as below:
CREATE TABLE testes (id serial, name text, face cube);
CREATE INDEX testes_face_idx ON testes USING gist(face gist_cube_ops);
explain ...
- 111
1
vote
1
answer
517
views
How to define operator class for composite type in PostgreSQL?
I have a composite type. And I want to define exclusion constraint on it, that would also be combined with range exclusions, but getting the following error.
create type example_t as (
x uuid,
...
- 1,121
0
votes
0
answers
377
views
Postgis GIST index - no performance increase
I have a Postgres 9.5 database with Postgis installed. I created a materialized view that is made from about 200 tables. The entire view contains 3,447,885 records. Each record has a location field ...
- 131
1
vote
0
answers
40
views
Speedup of Time range Queries
Situation:
Table Waypoints is relatively large (150M rows) and contains (amongst others) the columns user_id and a timestamp tracked_at.
Table Storyline is a smaller Table (300K rows) that contains ...
- 671
0
votes
1
answer
2k
views
Postgres: Set unique index on JSON field of array type
There is a table like following:
CREATE TABLE test (my_field jsonb NOT NULL)
The my_field has a key like collection which stores values in varchar[] format:
{my_field: {collection: ['a', 'b', 'c']}}
...
- 421
3
votes
1
answer
4k
views
ERROR: data type inet has no default operator class for access method "gist"
Using PostgreSQL version 9.4.5, I have a table that contains an INET column.
In that column I have a gist index
CREATE TABLE ipv4_prefix (
id uuid DEFAULT public.uuid_generate_v4() NOT NULL,
...
- 1,058
1
vote
1
answer
1k
views
Postgres ordering table by element in large data set
I have a tricky problem trying to find an efficient way of ordering a set of objects (~1000 rows) that contain a large (~5 million) number of indexed data points. In my case I need a query that allows ...
- 2,502
2
votes
1
answer
956
views
How to check if an unbounded range is NULL on the right in PostgreSQL 9.3 - ensuring GIST indexes are used
I'm using range datatypes in PG 9.3 (with btree_gist enabled, though I don't think it matters). I have GIST indexes that include these range columns. Some are int8range and some are tsrange.
I want ...
- 2,701
7
votes
1
answer
10k
views
PostgreSQL daterange not using index correctly
I have a simple table which has a user_birthday field with a type of date (which can be
NULL value)
CREATE TABLE users
(
user_id bigserial NOT NULL,
user_email text NOT NULL,
user_password text,...
- 2,867
16
votes
3
answers
9k
views
How to use uuid with postgresql gist index type?
I can't use directly uuid with gist index
CREATE INDEX idx_leaderboads_values_gist
ON leaderboard_entry
USING gist
(id_leaderboard , value);
And I got this error:
ERROR: data type uuid has ...
- 549
4
votes
1
answer
2k
views
PostgreSQL "IS [NOT] DISTINCT FROM" operator
Let's suppose a table:
CREATE TABLE foo (
id serial primary key
, range int4range NOT NULL
, barid integer references bar(id)
, baz whatever... NOT NULL
, EXCLUDE USING gist (range WITH &...
- 4,055
11
votes
1
answer
3k
views
Postgres hstore: GIN vs GiST index performance
I have to decide whether to use GIN or GiST indexing for an hstore column.
The Postgres docs state:
GIN index lookups are about three times faster than GiST
GIN indexes take about three times longer ...
- 4,356
4
votes
2
answers
3k
views
how can I detect gin and gist
how can I detect GIN and GiST indexes in postgresql? I am looking for if an database of postgres use fulltext. I think that a table use GIN o GiST then is using fulltext.
I accept that GIN or GiST ...
- 53
10
votes
4
answers
18k
views
PostgreSQL index not used for query on IP ranges
I'm using PostgreSQL 9.2 and have a table of IP ranges. Here's the SQL:
CREATE TABLE ips (
id serial NOT NULL,
begin_ip_num bigint,
end_ip_num bigint,
country_name character varying(255),
...
- 527
17
votes
4
answers
14k
views
What's the difference between B-Tree and GiST index methods (in PostgreSQL)?
I have been working on optimizing my Postgres databases recently, and traditionally, I've only ever use B-Tree indexes. However, I saw that GiST indexes suport non-unique, multicolumn indexes, in the ...
- 25.9k
40
votes
1
answer
21k
views
PostgreSQL: GIN or GiST indexes?
From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in ...
- 13.2k