Stack Overflow
Stack Overflow

Questions tagged [partial-index]

A partial index is an index built over a subset of a table; This is database technique possible in PostgreSQL

42 questions
2
votes
2 answers

'one-to-many' relation integrity issue for time ranges

Suppose I have table like: CREATE TABLE foo ( id SERIAL PRIMARY KEY , barid integer NOT NULL REFERENCES bar(id) , bazid integer NOT NULL REFERENCES baz(id) , startdate timestamp(0) NOT NULL , enddate timestamp(0) NOT NULL ); The purpose…
murison
  • 3,640
  • 2
  • 23
  • 36
1
vote
1 answer

Postgres insert is happening inspite of violating partial index constraint

Hi we have a table named billing_infos , where a partial index is created on three fields,account_id, state_id and deleted_at where deleted_at is NULL , My expectation is insert operation should fail on trying to insert duplicate values for…
lazarus
  • 677
  • 1
  • 13
  • 27
1
vote
0 answers

Postgres Partial Index using foreign column

Consider the following table with a partial index: CREATE TABLE triples( cardinality text NOT NULL, entity_id text NOT NULL, attribute text NOT NULL, value jsonb NOT NULL, ); CREATE UNIQUE INDEX triples_ea ON triples(entity_id, attribute)…
Stepan Parunashvili
  • 2,627
  • 5
  • 30
  • 51
1
vote
3 answers

Is it possible to index the position of an array column in PostgreSQL?

Let's say I want to find rows in the table my_table that have the value 5 at the first position of the array column my_array_column. To prepare the table, I executed the following statements: CREATE TABLE my_table ( id serial primary key, …
plenox
  • 15
  • 1
  • 5
1
vote
2 answers

DEFAULT is not allowed in this context error

Im trying to add an index to the database but I keep getting an error: PG::SyntaxError: ERROR: DEFAULT is not allowed in this context After reading the documentation for hours I can't seem to fix the issue. I'm running this: "CREATE UNIQUE INDEX…
joeyk16
  • 1,357
  • 22
  • 49
1
vote
2 answers

Update and insert performance with partial indexes

I have different queries for fetching data from a large table (about 100-200M rows). I've created partial indexes for my table with different predicates to fit the query because I know each query. For example, the table similar to this: CREATE TABLE…
Markeli
  • 558
  • 4
  • 16
1
vote
1 answer

How to index a multilanguage entity in PostgreSQL

Here I am creating table product_feature_text, having a 1:N relation with table product. As the application must support several user languages, a lang_code column is added to segment english texts from other language texts. As I want to present the…
coterobarros
  • 941
  • 1
  • 16
  • 25
1
vote
2 answers

Indexing the condition of a partial index in Postgres

I'm trying to reason about how Postgres partial indexes are stored inside Postgres. Suppose I create an index like this CREATE INDEX orders_unbilled_index ON orders (order_nr) WHERE billed is not true in order to quickly run a query like SELECT…
pir
  • 5,513
  • 12
  • 63
  • 101
1
vote
2 answers

Is this the right way to create a partial index in Postgres?

We have a table with 4 million records, and for a particular frequently used use-case we are only interested in records with a particular salesforce userType of 'Standard' which are only about 10,000 out of 4 million. The other usertype's that…
user2359997
  • 561
  • 1
  • 16
  • 40
1
vote
1 answer

Generic partial Index support for sqlalchemy

Along the lines of this question, SQLAlchemy - SQLite for testing and Postgresql for development - How to port? I realize, that the (above) the consensus is don't test with a db not used in production. I want to abstract partial index support for…
Nevermore
  • 7,141
  • 5
  • 42
  • 64
1
vote
1 answer

Duplicate data is sometimes inserted with unique index

I do have an unique index with a partialFilterExpression on a collection but duplicate data is sometimes inserted. Index creation getCollection().createIndex(new BasicDBObject(userId, 1) , new BasicDBObject("name", "uidx-something-user") …
Zarathustra
  • 2,853
  • 4
  • 33
  • 62
1
vote
1 answer

What is the correct way to optimize and/or index this query?

I've got a table pings with about 15 million rows in it. I'm on postgres 9.2.4. The relevant columns it has are a foreign key monitor_id, a created_at timestamp, and a response_time that's an integer that represents milliseconds. Here is the exact…
Brandon Weiss
  • 591
  • 1
  • 5
  • 14
1
vote
1 answer

Non-selective partial index on nulls

I've just found an interesting partial index in my db: CREATE INDEX orders_idx ON orders USING btree (status) WHERE status IS NULL; As you see it's completely non-selective and imho completely not usefull, or am I missing something?
Borys
  • 2,676
  • 2
  • 24
  • 37
1
vote
0 answers

Hibernate Unique Indices with condition

I am using Hibernate 3.6 with anontations, JPA and a postgres database. I am trying to set up an index for a table containing several columns and a condition on one column. The SQL is supposed to look like this: CREATE UNIQUE INDEX…
j.bergmann
  • 41
  • 7
0
votes
1 answer

Partial index not used on count

Hello and thanks in advance to anyone who will take some time to answer me. This is a question to learn how to use indexes efficiently on count queries. Version Postgresql 12.11 I have a huge table lots (~15M rows) which should have been partitioned…
Andrea Salicetti
  • 2,423
  • 24
  • 37
1
2
3