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
18
votes
2 answers
Workaround in mysql for partial Index or filtered Index?
I am using mysql db. I know postgresql and SQL server supports partial Indexing. In my case I want to do something like this:
CREATE UNIQUE INDEX myIndex ON myTable (myColumn) where myColumn <> 'myText'
I want to create a unique constraint but it…
Andrews
- 895
- 3
- 15
- 30
15
votes
1 answer
Add constraint to make column unique per group of rows
There is a column status in a Postgres table which can take only two values: Active and Inactive.
One of the columns is named userid. The table can have multiple rows with the same userid but at most one of them can have status = 'Active'. I need…
Shashwat Kumar
- 5,159
- 2
- 30
- 66
8
votes
2 answers
Partial Index not used in ON CONFLICT clause while performing an upsert in Postgresql
I have the following Entity Attribute value table :
CREATE TABLE key_value_pair (
id serial NOT NULL PRIMARY KEY,
key varchar(255) NOT NULL,
value varchar(255),
is_active boolean
);
CREATE UNIQUE INDEX…
Vaibhav
- 537
- 1
- 7
- 21
8
votes
1 answer
What are the limitations of partial indexes?
The latest version of MongoDB (v3.2) adds support for partial (filtered) indexes. You supply a filter when you create the index and that filter determines which documents will be referenced in the index and which will not.
Can I use any filter…
i3arnon
- 113,022
- 33
- 324
- 344
7
votes
1 answer
Partial index on timestamp against current time
I have a query where I filter the rows by comparing their insertion timestamps by five months ago.
This field does not get updated, we may think of it immutable if it helps.
CREATE TABLE events (
id serial PRIMARY KEY,
inserted_at timestamp…
Buğra Ekuklu
- 3,049
- 2
- 17
- 28
4
votes
0 answers
SQLAlchemy Postgres upsert with partial index
I have a table called Person with three fields - ID, city and name. City can be null, so I have two partial unique indexes - one on ID and city where city is not null, and one on ID where city is null.
Now I want to have an upsert statement using…
Aviad Nissel
- 337
- 3
- 14
3
votes
0 answers
Partial Unique Index in Nested Array
My documents are like this:
{ a: [ { u: 1 }, {} ] },
{ a: [ { u: 2 }, {}, {} ] },
{ a: [ { u: 3 } ] }
And I want to create unique index on a.u if it exists:
{
key: { 'a.u': 1 } },
unique: true,
partialFilterExpression: { 'a.u': {…
Derek Hsu
- 1,158
- 11
- 15
3
votes
0 answers
postgres index with date in where clause
I have large table with several million rows in Postgresql 9.1. One of the columns is timestamp with time zone.
Frequently used query is looking for data using where clause 'column > (now()::date - 11)' to look for last ten days.
I want to build an…
Martins Untals
- 2,128
- 2
- 18
- 31
2
votes
2 answers
Postgres partial vs regular / full index on nullable column
I have a table with 1m records, with 100k records having null on colA. Remaining records have pretty distinct values, is there a difference in creating a regular index on this column vs a partial index with where colA is not null?
Since regular…
nmakb
- 1,069
- 1
- 17
- 35
2
votes
0 answers
rails migration partial index's where not working as expected
i have a rails migration script:
add_index :site_tracking_codes, [:site_id, :is_published], where: "is_published IS TRUE", unique: true
but it generates the index like so:
CREATE UNIQUE INDEX index_site_tracking_codes_on_site_id_and_is_published ON…
Tanya
- 55
- 1
- 1
- 8
2
votes
1 answer
When do partial indexes get updated in PostgreSQL?
I am doing a performance tuning on one of the largest table in our project. While reading about indexes I came across partial indexes. This sounds like a very nice idea to include only rows which are getting accessed frequently.
But I am not able to…
Prometheus
- 549
- 1
- 7
- 18
2
votes
0 answers
PostgreSQL using timestamp difference in partial index for upsert
I need to get real-time data and put it into a Postgres table so compare the columns oid and rcv_time respectively with newly received ones.
If this oid previously has been inserted and its received time is more than two hours from now should be…
fatemeh poormohammad
- 62
- 9
2
votes
1 answer
MongoDB & Partial indexes : Avoid filtering stage when filtering on null date
I am trying to optimize a DB lookup as best as I can. From what I understand, my goal should be to target a winningPlan where the only stage is an IXScan. But I have a field containing date keys, and it seems like I cannot build a compound index…
Cyril Duchon-Doris
- 12,964
- 9
- 77
- 164
2
votes
3 answers
Simple WHERE EXISTS ... ORDER BY... query very slow in PostrgeSQL
I have this very simple query, generated by my ORM (Entity Framework Core):
SELECT *
FROM "table1" AS "t1"
WHERE EXISTS (
SELECT 1
FROM "table2" AS "t2"
WHERE ("t2"."is_active" = TRUE) AND ("t1"."table2_id" = "t2"."id"))
ORDER BY…
olivierr91
- 1,243
- 3
- 13
- 29
2
votes
1 answer
How does a unique partial index work inside an array in Mongo
Does a Mongo partial filter work document wide or is it capable of applying to each individual array item?
USE CASE:
Schema
{ references: [
{source: ObjectId("57dc688a1410a81ba0fcafcd"), sourceId: 1234},
{source:…
Brock
- 71
- 4