Table Indexing on PostgreSQL for performance

Question

I am solving performance issues on PostgreSQL and I have the following table:

 CREATE TABLE main_transaction (
   id integer NOT NULL DEFAULT nextval('main_transaction_id_seq'::regclass),
   description character varying(255) NOT NULL,
   request_no character varying(18),
   account character varying(50),
   ....
 )

Above table has 34 columns including 3 FOREIGN KEYs and it has over 1 Million rows data. I have the following conditional SELECT query:

SELECT * FROM main_transaction
WHERE upper(request_no) LIKE upper(concat('%','20080417-0258-0697','%'))

Returning the result in over 2 seconds. I want to decrease working time by using table indexing. So far, I have used btree indexing. However, I didn't notice any fast result. My question is, how can I improve performance for above query?

Why the %? the search string already has the maximum length for the column... — Laurenz Albe
– Laurenz Albe, Commented Apr 20, 2018 at 10:06

Laurenz Albe · Accepted Answer · 2018-04-20 10:12:01Z

2

Your only chance to search for a pattern that begins with % is a trigram index:

CREATE EXTENSION pg_trgm;

CREATE INDEX ON main_transaction
   USING gin (upper(request_no) gin_trgm_ops);

answered Apr 20, 2018 at 10:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

3 Comments

Sanatbek Matlatipov Over a year ago

Great! I also have foreign keys in my table and I am using CROSS JOIN to link the tables by id and it is causing slow performance. I have a profile_id column in main_transaction table for profile table and in profile table I have customer_id for customer table.

SELECT * FROM main_transaction t CROSS JOIN main_profile p CROSS JOIN main_customer c  WHERE t.profile_id = p.id AND p.user_id = c.id AND ( 	upper(t.request_no) LIKE upper(concat('%','0-90-6 12 ','%')) 	OR upper(c.phone) LIKE upper(concat('%','0-90-6  12','%')) )

. Thank you

Laurenz Albe Over a year ago

Well, then open a question for it.

Sanatbek Matlatipov Over a year ago

here is the url :). stackoverflow.com/questions/49942061/…

Collectives™ on Stack Overflow

Table Indexing on PostgreSQL for performance

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related