Postgres data base need suggestion creating an index for table

Question

I a have a table structure as below. For fetching the data from table I am having search criteria as mentioned below. I am writing a singe sql query as per requirement(sample query I mentioned below). I need to create an index for the table to cover all the search criteria. It will be helpful somebody advice me.

Table structure(columns):

applicationid varchar(15),
trans_tms timestamp,
SSN varchar,
firstname varchar,
lastname varchar,
DOB date,
Zipcode smallint,
adddetais json

Search criteria will be from API will be fall under 4 categories. All 4 categories are mandatory. At any cost I will receive 4 categories of values for against single applicant.

Search criteria:

ssn&last name (last name need to use function I.e. soundex(lastname)=soundex('inputvalue').
ssn & DOB
ssn&zipcode
firstname&lastname&DOB.

Query:

I am trying to write. Sample query is:

Select * 
from table 
where ((ssn='aaa' and soundex(lastname)=soundex('xxx') 
       or ((ssn='aaa' and dob=xxx) 
       or (ssn='aaa' and zipcode = 'xxx') 
       or (firstname='xxx' and lastname='xxx' and dob= xxxx));

For considering performance I need to create an index for the table. Might be composite. Any suggestion will be helpful.

Please provide proper information for performance questions. Consider instructions here: stackoverflow.com/tags/postgresql-performance/info — Erwin Brandstetter
– Erwin Brandstetter, Commented Oct 12, 2021 at 17:11

saurabh bhadane · Accepted Answer · 2021-10-12 18:17:59Z

0

Some Approaches I would follow:

Yes, you are correct composite index/multicolumn index will give benefit in AND conditions of two columns, however, indexes would overlap on columns for given conditions. Documentation : https://www.postgresql.org/docs/10/indexes-multicolumn.html
You can use a UNION instead of OR.

Reference : https://www.cybertec-postgresql.com/en/avoid-or-for-better-performance/

If multiple conditions could be combined for e.g: ssn should be 'aaa' with any combination, then modifying the where clause with lesser OR is preferable.

answered Oct 12, 2021 at 18:17

saurabh bhadane

2211 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shagul Over a year ago

Thanks Saurabh for response. considering above fields and I mentioned all 4 categories are mandate. I am thinking to go with composite index like below. if prior that anything needs to be taken into account. any impact based on this. I am new to Postgres too. CREATE INDEX test_dupapp_1 ON DUPAPPCHECK using btree (ssn,soundex(lastname)); CREATE INDEX test_dupapp_2 ON DUPAPPCHECK using btree (ssn,dob); CREATE INDEX test_dupapp_3 ON DUPAPPCHECK using btree (ssn,zipcode); CREATE INDEX test_dupapp_4 ON DUPAPPCHECK using btree (upper(lastname),upper(firstname),dob);

Collectives™ on Stack Overflow

Postgres data base need suggestion creating an index for table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related