0

I have more than 1 million rows in the table and my SELECT query is slow when I search between date range . So, I have the following table

CREATE TABLE public.main_transaction
(
  id integer NOT NULL DEFAULT nextval('main_transaction_id_seq'::regclass),
  description character varying(255) NOT NULL,
  transaction_type character varying(18) NOT NULL,
  pub_date timestamp with time zone NOT NULL,
  ...
)

Above table has 34 columns including 3 FOREIGN KEYs and
I want to speed up the following query.

SELECT * FROM main_transaction 
WHERE 
   pub_date >= '2018-01-01'
   AND pub_date <'2018-01-08'

Now, it is working in 12 secs. :(

By the way, I have tried this solution. However, I didn't notice much change.

execution plan:

Seq Scan on main_transaction t (cost=0.00..74437.33 rows=28974 width=1455)
  Filter: ((pub_date >= '2018-01-01 00:00:00+05'::timestamp with time zone) AND (pub_date < '2018-01-08 00:00:00+05'::timestamp with time zone))
Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Sanatbek_Matlatipov
  • 188
  • 5
  • 16
  • execution plan please – Vao Tsun May 01 '18 at 12:36
  • What index definition do you have? – Ben May 01 '18 at 12:39
  • @Vao Tsun "Seq Scan on main_transaction t (cost=0.00..74437.33 rows=28974 width=1455)" " Filter: ((pub_date >= '2018-01-01 00:00:00+05'::timestamp with time zone) AND (pub_date < '2018-01-08 00:00:00+05'::timestamp with time zone))" – Sanatbek_Matlatipov May 01 '18 at 12:41
  • @Ben Please refer to the following links to understand my table [1-link](https://stackoverflow.com/questions/49942061/postgresql-cross-join-indexing-for-performance) and [2-link](https://stackoverflow.com/questions/49938650/table-indexing-on-postgresql-for-performance) – Sanatbek_Matlatipov May 01 '18 at 12:44
  • Laurenz suggests creating index in your refered answer - what you mean it did not help? how you checked that? analyzed table and checked the plan?.. – Vao Tsun May 01 '18 at 12:45
  • Performance is the same, after indexing as @Laurenz said. – Sanatbek_Matlatipov May 01 '18 at 12:50
  • Please put the relevant information into this question. – Ben May 01 '18 at 13:38
  • @Ben Please can you clarify what do you mean by relevant? And, what is irrelevant in my question. – Sanatbek_Matlatipov May 01 '18 at 14:09
  • Please put the needed information in the question. E.g. the index definitions. – Ben May 01 '18 at 14:11
  • @ben Please open 1-link and 2-link as I mentioned above comment. There are explained all Constraints and index definitions. Laurenz helped me to do my indexing before. So, no more indexes defined except those links. – Sanatbek_Matlatipov May 01 '18 at 15:02
  • Please put the information required for **this** question into **this** question. That's how this site is supposed to work. – Ben May 01 '18 at 15:11

0 Answers0