I have a very important query in my system that is taking too long to execute due to huge amount of data on tables. I'm a junior DBA and i need the best optimization I can get for this. Tables have approximate 80 million rows each.
Tables are:
tb_pd
:
Column | Type | Modifiers | Storage | Stats target | Description
---------------------+---------+-----------+---------+--------------+-------------
pd_id | integer | not null | plain | |
st_id | integer | | plain | |
status_id | integer | | plain | |
next_execution_date | bigint | | plain | |
priority | integer | | plain | |
is_active | integer | | plain | |
Indexes:
"pk_pd" PRIMARY KEY, btree (pd_id)
"idx_pd_order" btree (priority, next_execution_date)
"idx_pd_where" btree (status_id, next_execution_date, is_active)
Foreign-key constraints:
"fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)
tb_st
:
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
st_id | integer | not null | plain | |
st | character varying(500) | | extended | |
Indexes:
"pk_st" PRIMARY KEY, btree (st_id)
Referenced by:
TABLE "tb_pd" CONSTRAINT "fk_st" FOREIGN KEY (st_id) REFERENCES tb_st(st_id)
My query is:
select s.st
from tb_pd p inner join
tb_st s on p.st_id = s.st_id
where p.status_id = 1 and
p.next_execution_date < 1401402110830 and
p.is_active = 1
order by priority, next_execution_date
limit 20000;
With the indexes I have, the best I got was:
Limit (cost=1.14..263388.65 rows=20000 width=45)
-> Nested Loop (cost=1.14..456016201.43 rows=34627017 width=45)
-> Index Scan using idx_pd_order on tb_pd p (cost=0.57..161388942.77 rows=34627017 width=16)
Index Cond: (next_execution_date < 1401402110830::bigint)
Filter: ((status_id = 1) AND (is_active = 1))
-> Index Scan using pk_st on tb_st s (cost=0.57..8.50 rows=1 width=37)
Index Cond: (st_id = p.st_id)
I cannot understand the explain very well but it's not using the idx_pd_where
to filter the where clause. The idx_pd_where
have all the columns used in where clause.
More info about data:
status_id
is 95% = 1
is_active
is 90% = 1
next_execution_date
is in millis and varies a lot. The value compared is the moment of the execution (current time in millis)
Should I create separate indexes for each filtered column or use any different kind of indexes? Maybe some configuration on DBMS?
priority
,status_id
andis_active
? The crucial column ispriority
and of all columns you forgot to give details for that one.