Performance increasing for search form with date range in Postgresql
I have a search form where 4 parameters passed. They are:
:status -> string and can be ['SUCCESS', 'FAILURE', 'INPROGRESS'], :partner -> string, :fromDate -> timestamp with time zone, :toDate-> timestamp with time zone,
I am solving performance issues for my search form to the following query
SELECT t.request_no, t.MODE, t.service_type, t.amount_in_som, t.description, t.status FROM main_transaction t WHERE (:status = 'UNKNOWN' OR t.status = :status) --if UNKNOWN means select all AND t.transaction_type = 'WITHDRAW' AND t.status <> 'INPROGRESS' AND t.pub_date >='2018-10-01' -- :fromDate AND t.pub_date <='2018-11-07' -- :toDate AND CASE WHEN :partner ='A1' AND ( t.service_type = 'SERVICE_1' OR t.service_type = 'SERVICE_2' OR t.service_type = 'SERVICE_3' ) AND (t.MODE = 'AUTO' OR t.MODE = 'AUTO_1' OR t.MODE = 'AUTO_2' OR t.MODE = 'AUTO_3') THEN TRUE WHEN :partner ='A2' AND ( t.service_type = 'SERVICE_1' OR t.service_type = 'SERVICE_2') AND t.profile_id = 4287 THEN TRUE ELSE FALSE END = TRUE
Returning the result in over 4 seconds. I want to decrease working time by using table indexing or something else? So, how can I increase the performance for this query?
I am selecting from 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), service_type character varying(50), pub_date" timestamptz(6) NOT NULL, "service_id" varchar(50) COLLATE "pg_catalog"."default", .... )
Above table has 34 columns including 3 FOREIGN KEYs and it has over 3 Million rows data.
"Seq Scan on main_transaction transactio0_ (cost=0.00..322285.41 rows=103067 width=88) (actual time=0.248..2721.617 rows=158846 loops=1)" " Filter: (((status)::text <> 'INPROGRESS'::text) AND (pub_date >= '2018-10-01 00:00:00+05'::timestamp with time zone) AND (pub_date <= '2018-11-07 00:00:00+05'::timestamp with time zone) AND ((transaction_type)::text = 'WITHDRAW'::text) AND CASE WHEN ((((service_type)::text = 'SERVICE_1'::text) OR ((service_type)::text = 'SERVICE_2'::text) OR ((service_type)::text = 'SERVICE_3'::text)) AND (((mode)::text = 'AUTO'::text) OR ((mode)::text = 'AUTO_1'::text) OR ((mode)::text = 'AUTO_2'::text) OR ((mode)::text = 'AUTO_3'::text))) THEN true ELSE false END)" " Rows Removed by Filter: 2567005" " Buffers: shared hit=15344 read=204722" "Planning time: 0.232 ms" "Execution time: 2729.562 ms"