Run Aidbox locallyRun Aidbox in SandboxTalk to us Ask community

Create indexes manually

The following indexes cover regular searches, e.g. Patient?name=Smith. Please contact us if you need more examples.

Account

Search parameter: Account.status, Account.type, Account.subject, Account.owner, Account.identifier, Account.patient

CREATE INDEX CONCURRENTLY
IF NOT EXISTS account_resource__gin
ON "account"
USING GIN (resource);

Search parameter: Account.name

CREATE INDEX CONCURRENTLY
IF NOT EXISTS account_resource__name_gin_trgm
ON "account"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["name"]]$JSON$))) gin_trgm_ops);

Search parameter: Account.period

CREATE INDEX CONCURRENTLY
IF NOT EXISTS account_resource__period_max
ON "account"
USING btree (knife_extract_max_timestamptz(resource, '[["servicePeriod","start"],["servicePeriod","end"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS account_resource__period_min
ON "account"
USING btree (knife_extract_min_timestamptz(resource, '[["servicePeriod","start"],["servicePeriod","end"]]'));

ActivityDefinition

Search parameter: ActivityDefinition.publisher

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__publisher_gin_trgm
ON "activitydefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["publisher"]]$JSON$))) gin_trgm_ops);

Search parameter: ActivityDefinition.version, ActivityDefinition.context-type, ActivityDefinition.status, ActivityDefinition.identifier, ActivityDefinition.jurisdiction, ActivityDefinition.topic, ActivityDefinition.composed-of, ActivityDefinition.url, ActivityDefinition.predecessor, ActivityDefinition.context, ActivityDefinition.successor, ActivityDefinition.derived-from, ActivityDefinition.depends-on

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__gin
ON "activitydefinition"
USING GIN (resource);

Search parameter: ActivityDefinition.name

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__name_gin_trgm
ON "activitydefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["name"]]$JSON$))) gin_trgm_ops);

Search parameter: ActivityDefinition.title

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__title_gin_trgm
ON "activitydefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["title"]]$JSON$))) gin_trgm_ops);

Search parameter: ActivityDefinition.description

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__description_gin_trgm
ON "activitydefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["description"]]$JSON$))) gin_trgm_ops);

Search parameter: ActivityDefinition.context-quantity

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__context_quantity_max
ON "activitydefinition"
USING btree (knife_extract_max_numeric(resource, '[["useContext","value","Quantity","value"],["useContext","value","Range"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__context_quantity_min
ON "activitydefinition"
USING btree (knife_extract_min_numeric(resource, '[["useContext","value","Quantity","value"],["useContext","value","Range"]]'));

Search parameter: ActivityDefinition.effective

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__effective_max
ON "activitydefinition"
USING btree (knife_extract_max_timestamptz(resource, '[["effectivePeriod","start"],["effectivePeriod","end"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__effective_min
ON "activitydefinition"
USING btree (knife_extract_min_timestamptz(resource, '[["effectivePeriod","start"],["effectivePeriod","end"]]'));

Search parameter: ActivityDefinition.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__date_max
ON "activitydefinition"
USING btree (knife_extract_max_timestamptz(resource, '[["date"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS activitydefinition_resource__date_min
ON "activitydefinition"
USING btree (knife_extract_min_timestamptz(resource, '[["date"]]'));

AdverseEvent

Search parameter: AdverseEvent.study, AdverseEvent.subject, AdverseEvent.location, AdverseEvent.seriousness, AdverseEvent.substance, AdverseEvent.recorder, AdverseEvent.category, AdverseEvent.event, AdverseEvent.actuality, AdverseEvent.severity, AdverseEvent.resultingcondition

CREATE INDEX CONCURRENTLY
IF NOT EXISTS adverseevent_resource__gin
ON "adverseevent"
USING GIN (resource);

Search parameter: AdverseEvent.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS adverseevent_resource__date_max
ON "adverseevent"
USING btree (knife_extract_max_timestamptz(resource, '[["date"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS adverseevent_resource__date_min
ON "adverseevent"
USING btree (knife_extract_min_timestamptz(resource, '[["date"]]'));

AllergyIntolerance

Search parameter: AllergyIntolerance.criticality, AllergyIntolerance.type, AllergyIntolerance.category, AllergyIntolerance.route, AllergyIntolerance.patient, AllergyIntolerance.identifier, AllergyIntolerance.verification-status, AllergyIntolerance.severity, AllergyIntolerance.manifestation, AllergyIntolerance.asserter, AllergyIntolerance.recorder, AllergyIntolerance.clinical-status, AllergyIntolerance.code

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__gin
ON "allergyintolerance"
USING GIN (resource);

Search parameter: AllergyIntolerance.last-date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__last_date_max
ON "allergyintolerance"
USING btree (knife_extract_max_timestamptz(resource, '[["lastOccurrence"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__last_date_min
ON "allergyintolerance"
USING btree (knife_extract_min_timestamptz(resource, '[["lastOccurrence"]]'));

Search parameter: AllergyIntolerance.onset

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__onset_max
ON "allergyintolerance"
USING btree (knife_extract_max_timestamptz(resource, '[["reaction","onset"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__onset_min
ON "allergyintolerance"
USING btree (knife_extract_min_timestamptz(resource, '[["reaction","onset"]]'));

Search parameter: AllergyIntolerance.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__date_max
ON "allergyintolerance"
USING btree (knife_extract_max_timestamptz(resource, '[["recordedDate"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS allergyintolerance_resource__date_min
ON "allergyintolerance"
USING btree (knife_extract_min_timestamptz(resource, '[["recordedDate"]]'));

Appointment

Search parameter: Appointment.part-status, Appointment.slot, Appointment.supporting-info, Appointment.based-on, Appointment.reason-code, Appointment.identifier, Appointment.specialty, Appointment.status, Appointment.service-category, Appointment.appointment-type, Appointment.actor, Appointment.reason-reference, Appointment.practitioner, Appointment.service-type, Appointment.patient, Appointment.location

CREATE INDEX CONCURRENTLY
IF NOT EXISTS appointment_resource__gin
ON "appointment"
USING GIN (resource);

Search parameter: Appointment.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS appointment_resource__date_max
ON "appointment"
USING btree (knife_extract_max_timestamptz(resource, '[["start"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS appointment_resource__date_min
ON "appointment"
USING btree (knife_extract_min_timestamptz(resource, '[["start"]]'));

AppointmentResponse

Search parameter: AppointmentResponse.practitioner, AppointmentResponse.actor, AppointmentResponse.part-status, AppointmentResponse.patient, AppointmentResponse.appointment, AppointmentResponse.identifier, AppointmentResponse.location

CREATE INDEX CONCURRENTLY
IF NOT EXISTS appointmentresponse_resource__gin
ON "appointmentresponse"
USING GIN (resource);

AuditEvent

Search parameter: AuditEvent.action, AuditEvent.source, AuditEvent.type, AuditEvent.agent-role, AuditEvent.policy, AuditEvent.entity-type, AuditEvent.outcome, AuditEvent.agent, AuditEvent.entity-role, AuditEvent.altid, AuditEvent.subtype, AuditEvent.entity, AuditEvent.site, AuditEvent.patient

CREATE INDEX CONCURRENTLY
IF NOT EXISTS auditevent_resource__gin
ON "auditevent"
USING GIN (resource);

Search parameter: AuditEvent.address

CREATE INDEX CONCURRENTLY
IF NOT EXISTS auditevent_resource__address_gin_trgm
ON "auditevent"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["agent","network","address"]]$JSON$))) gin_trgm_ops);

Search parameter: AuditEvent.entity-name

CREATE INDEX CONCURRENTLY
IF NOT EXISTS auditevent_resource__entity_name_gin_trgm
ON "auditevent"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["entity","name"]]$JSON$))) gin_trgm_ops);

Search parameter: AuditEvent.agent-name

CREATE INDEX CONCURRENTLY
IF NOT EXISTS auditevent_resource__agent_name_gin_trgm
ON "auditevent"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["agent","name"]]$JSON$))) gin_trgm_ops);

Search parameter: AuditEvent.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS auditevent_resource__date_max
ON "auditevent"
USING btree (knife_extract_max_timestamptz(resource, '[["recorded"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS auditevent_resource__date_min
ON "auditevent"
USING btree (knife_extract_min_timestamptz(resource, '[["recorded"]]'));

Basic

Search parameter: Basic.subject, Basic.author, Basic.identifier, Basic.patient, Basic.code

CREATE INDEX CONCURRENTLY
IF NOT EXISTS basic_resource__gin
ON "basic"
USING GIN (resource);

Search parameter: Basic.created

CREATE INDEX CONCURRENTLY
IF NOT EXISTS basic_resource__created_max
ON "basic"
USING btree (knife_extract_max_timestamptz(resource, '[["created"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS basic_resource__created_min
ON "basic"
USING btree (knife_extract_min_timestamptz(resource, '[["created"]]'));

BodyStructure

Search parameter: BodyStructure.location, BodyStructure.morphology, BodyStructure.patient, BodyStructure.identifier

CREATE INDEX CONCURRENTLY
IF NOT EXISTS bodystructure_resource__gin
ON "bodystructure"
USING GIN (resource);

Bundle

Search parameter: Bundle.type, Bundle.identifier

CREATE INDEX CONCURRENTLY
IF NOT EXISTS bundle_resource__gin
ON "bundle"
USING GIN (resource);

Search parameter: Bundle.composition

NOT SUPPORTED

Search parameter: Bundle.message

NOT SUPPORTED

Search parameter: Bundle.timestamp

CREATE INDEX CONCURRENTLY
IF NOT EXISTS bundle_resource__timestamp_max
ON "bundle"
USING btree (knife_extract_max_timestamptz(resource, '[["timestamp"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS bundle_resource__timestamp_min
ON "bundle"
USING btree (knife_extract_min_timestamptz(resource, '[["timestamp"]]'));

CarePlan

Search parameter: CarePlan.goal, CarePlan.category, CarePlan.status, CarePlan.identifier, CarePlan.condition, CarePlan.activity-code, CarePlan.instantiates-uri, CarePlan.performer, CarePlan.instantiates-canonical, CarePlan.patient, CarePlan.intent, CarePlan.encounter, CarePlan.subject, CarePlan.part-of, CarePlan.activity-reference, CarePlan.based-on, CarePlan.replaces, CarePlan.care-team

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careplan_resource__gin
ON "careplan"
USING GIN (resource);

Search parameter: CarePlan.activity-date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careplan_resource__activity_date_max
ON "careplan"
USING btree (knife_extract_max_timestamptz(resource, '[["activity","detail","scheduled","Period","start"],["activity","detail","scheduled","Period","end"],["activity","detail","scheduled","string"],["activity","detail","scheduled","Timing","event"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careplan_resource__activity_date_min
ON "careplan"
USING btree (knife_extract_min_timestamptz(resource, '[["activity","detail","scheduled","Period","start"],["activity","detail","scheduled","Period","end"],["activity","detail","scheduled","string"],["activity","detail","scheduled","Timing","event"]]'));

Search parameter: CarePlan.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careplan_resource__date_max
ON "careplan"
USING btree (knife_extract_max_timestamptz(resource, '[["period","start"],["period","end"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careplan_resource__date_min
ON "careplan"
USING btree (knife_extract_min_timestamptz(resource, '[["period","start"],["period","end"]]'));

CareTeam

Search parameter: CareTeam.identifier, CareTeam.subject, CareTeam.category, CareTeam.patient, CareTeam.participant, CareTeam.encounter, CareTeam.status

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careteam_resource__gin
ON "careteam"
USING GIN (resource);

Search parameter: CareTeam.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careteam_resource__date_max
ON "careteam"
USING btree (knife_extract_max_timestamptz(resource, '[["period","start"],["period","end"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS careteam_resource__date_min
ON "careteam"
USING btree (knife_extract_min_timestamptz(resource, '[["period","start"],["period","end"]]'));

ChargeItem

Search parameter: ChargeItem.subject, ChargeItem.performer-function, ChargeItem.performer-actor, ChargeItem.enterer, ChargeItem.context, ChargeItem.requesting-organization, ChargeItem.account, ChargeItem.performing-organization, ChargeItem.patient, ChargeItem.identifier, ChargeItem.code, ChargeItem.service

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__gin
ON "chargeitem"
USING GIN (resource);

Search parameter: ChargeItem.entered-date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__entered_date_max
ON "chargeitem"
USING btree (knife_extract_max_timestamptz(resource, '[["enteredDate"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__entered_date_min
ON "chargeitem"
USING btree (knife_extract_min_timestamptz(resource, '[["enteredDate"]]'));

Search parameter: ChargeItem.price-override

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__price_override_max
ON "chargeitem"
USING btree (knife_extract_max_numeric(resource, '[["priceOverride","value"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__price_override_min
ON "chargeitem"
USING btree (knife_extract_min_numeric(resource, '[["priceOverride","value"]]'));

Search parameter: ChargeItem.quantity

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__quantity_max
ON "chargeitem"
USING btree (knife_extract_max_numeric(resource, '[["quantity","value"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__quantity_min
ON "chargeitem"
USING btree (knife_extract_min_numeric(resource, '[["quantity","value"]]'));

Search parameter: ChargeItem.occurrence

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__occurrence_max
ON "chargeitem"
USING btree (knife_extract_max_timestamptz(resource, '[["occurrence","Period","start"],["occurrence","Period","end"],["occurrence","dateTime"],["occurrence","Timing","event"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__occurrence_min
ON "chargeitem"
USING btree (knife_extract_min_timestamptz(resource, '[["occurrence","Period","start"],["occurrence","Period","end"],["occurrence","dateTime"],["occurrence","Timing","event"]]'));

Search parameter: ChargeItem.factor-override

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__factor_override_max
ON "chargeitem"
USING btree (knife_extract_max_numeric(resource, '[["factorOverride"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitem_resource__factor_override_min
ON "chargeitem"
USING btree (knife_extract_min_numeric(resource, '[["factorOverride"]]'));

ChargeItemDefinition

Search parameter: ChargeItemDefinition.identifier, ChargeItemDefinition.context-type, ChargeItemDefinition.url, ChargeItemDefinition.context, ChargeItemDefinition.status, ChargeItemDefinition.jurisdiction, ChargeItemDefinition.version

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__gin
ON "chargeitemdefinition"
USING GIN (resource);

Search parameter: ChargeItemDefinition.publisher

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__publisher_gin_trgm
ON "chargeitemdefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["publisher"]]$JSON$))) gin_trgm_ops);

Search parameter: ChargeItemDefinition.title

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__title_gin_trgm
ON "chargeitemdefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["title"]]$JSON$))) gin_trgm_ops);

Search parameter: ChargeItemDefinition.description

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__description_gin_trgm
ON "chargeitemdefinition"
USING GIN ((aidbox_text_search(knife_extract_text(resource, $JSON$[["description"]]$JSON$))) gin_trgm_ops);

Search parameter: ChargeItemDefinition.effective

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__effective_max
ON "chargeitemdefinition"
USING btree (knife_extract_max_timestamptz(resource, '[["effectivePeriod","start"],["effectivePeriod","end"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__effective_min
ON "chargeitemdefinition"
USING btree (knife_extract_min_timestamptz(resource, '[["effectivePeriod","start"],["effectivePeriod","end"]]'));

Search parameter: ChargeItemDefinition.context-quantity

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__context_quantity_max
ON "chargeitemdefinition"
USING btree (knife_extract_max_numeric(resource, '[["useContext","value","Quantity","value"],["useContext","value","Range"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__context_quantity_min
ON "chargeitemdefinition"
USING btree (knife_extract_min_numeric(resource, '[["useContext","value","Quantity","value"],["useContext","value","Range"]]'));

Search parameter: ChargeItemDefinition.date

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__date_max
ON "chargeitemdefinition"
USING btree (knife_extract_max_timestamptz(resource, '[["date"]]'));

CREATE INDEX CONCURRENTLY
IF NOT EXISTS chargeitemdefinition_resource__date_min
ON "chargeitemdefinition"
USING btree (knife_extract_min_timestamptz(resource, '[["date"]]'));

Claim

Search parameter: Claim.provider, Claim.patient, Claim.insurer, Claim.facility, Claim.encounter, Claim.care-team, Claim.identifier, Claim.payee,