A partial index is an index built over a subset of a table; This is database technique possible in PostgreSQL
Questions tagged [partial-index]
42 questions
0
votes
1 answer
Partial Index on Postgres where condition is in separate table
I have table schema as:
table_a:
id
active boolean
table_b:
id
table_a_id ForeignKey
I want to apply a partial unique index on table_b but only if active=True for table_a_id.
Is this feasible in postgres?
kushj
- 117
- 1
- 7
0
votes
1 answer
Aurora postgres Can I create a partial GIN index on a JSONB column
We have two different applications which will be using my search table.
They have two different filters that will be always be applied and it is different for each of the application. So i created a partial index as given below so that the filter is…
Balaji Govindan
- 1
- 2
0
votes
1 answer
postgres: partial Index on frequently update column
I'm new to postgres and I have a question about using partial index on frequently updated columns.
I have a huge table: job, which has the following columns. It has almost 50 millions rows.
Table and index
CREATE TABLE job
(
id uuid,
assigned_at…
shirleyl
- 1
- 1
0
votes
2 answers
How to create partial index in django 2.2 models
I have a model like this in Django 2.2
class Post(models.Model):
class Meta:
verbose_name = _('Post')
verbose_name_plural = _('Posts')
phone_number = models.CharField(max_length=11, verbose_name=_('Phone number'),…
Amin Bashiri
- 198
- 1
- 2
- 16
0
votes
1 answer
Indexing on a mostly 0 column not working
I've been having troubles with trying to use an index on my table.
For table items, I have a column named market. If market = 0, then it's not for sale - if it's a value greater than 0 (for example, 100), it is for sale, for $100.
However, I have…
Lego490
- 135
- 1
- 12
0
votes
1 answer
How to ensure uniqueness with NULL values in cockroach DB
I have a table of accounts which generates unique keys with a sequence. It is used to generate unique identities for accounts and account/user pairs.
I tried the following from pgsql:
CREATE INDEX ON accounts (account_id, user_id) WHERE user_id IS…
Coyote
- 2,454
- 26
- 47
0
votes
0 answers
Querying MongoDB collection with heterogeneous schema efficiently
I'm developing a web application with NodeJS, MongoDB and Mongoose. It is intended to act as an interface between the user and a big data environment. The idea is that the users can execute the big data processes in a separated cluster, and the…
Álvaro Valencia
- 1,187
- 8
- 17
0
votes
2 answers
MongoDB count partially indexed items
I have a partial index on a collection that looks sorta like this:
{ // Fields
"Name" : 1
}
{ // Options
"unique" : true,
"name" : "Name_1",
"partialFilterExpression" : {
"IsArchived" : {
"$eq" : true
}
…
Cory-G
- 1,025
- 14
- 26
0
votes
1 answer
Will it make a noticeable different in performance/index size if my index is made partial?
Say I have a table with 20 million rows I want to index like so:
CREATE INDEX fruit_color
ON fruits
USING btree
(color);
Now let's say that only 2% of the fruits have a color, rest will be NULL. My queries will NEVER want to find fruits with…
Niels Kristian
- 8,661
- 11
- 59
- 117
0
votes
0 answers
PostgreSQL: Statistics on partial index?
PostgreSQL version: 9.3.13
Consider the following tables, index and data:
CREATE TABLE orders (
order_id bigint,
status smallint,
owner int,
CONSTRAINT orders_pkey PRIMARY KEY (order_id)
)
CREATE INDEX owner_index ON orders
USING btree
…
HelloSam
- 2,225
- 1
- 20
- 21
0
votes
1 answer
Postgres how can I get column inside a quote
First and foremost I am using postgres version 9.4 . I am trying to create a partial index on this query
select DISTINCT ON(city,state)city,state,zip from zips where city ilike
'%' and state ilike '%' limit 10
My issue is that I do not know how to…
user1949387
- 1,245
- 3
- 21
- 38
-1
votes
1 answer
Can I create a partial index as shown in below?
Assume there is a table with 5 columns (id ,first_name (text),last_name(text), product_name(text) and available(boolean)).
CREATE INDEX IF NOT EXISTS "idx-firstname-lower + product_name"
ON persons (LOWER(first_name), product_name) WHERE…
KI0821
- 1
- 2