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?
Best Answer
This is a tricky one. Your main condition is on
next_execution_date
, but the output is sorted bypriority
first. The conditions onstatus_id
andis_active
only play a minor part.Better index
Your index
idx_pd_order
is not a big help, since filtering on non-leading columns of a multi-column index is not very efficient. Postgres is using it - still a lot better than a sequential scan. Details here:Is a composite index also good for queries on the first field?
idx_pd_where
might be a better choice, but not a good one, either. The leading columnstatus_id
is not selective at all and just bloats the index. Same goes for the trailing columnis_active
. Andpriority
is not in the index and has to be fetched from the table, making an index-only scan impossible.I suggest this partial, multi-column index to start with. (But keep reading!)
Since we are only ever interested in rows with
status_id = 1
andis_active = 1
exclude other rows from the index right away. Size does matter.The remaining (crucial) condition is on
next_execution_date
, which must come first in the index.priority
andst_id
are only appended for a possible index-only scan (Postgres 9.2+). If that doesn't take, remove the columns from the index to make is smaller.Special difficulty
We can now use
idx_pd_covering
to find qualifying rows quickly, unfortunately we have to look at all qualifying rows to collect the ones with highestpriority
. As the query plan reveals, Postgres estimates to process 34627017 rows. Sorting 35M rows is going to cost big. That's the tricky part I mentioned at the start. To demonstrate what I am talking about, runEXPLAIN
on your query with and withoutpriority
inORDER BY
:That's your query, formatted only slightly simplified. You should see a huge difference.
Solution
The solution depends on the number of distinct values for
priority
. For lack of information and for demo purposes I am going to assume only three. Priority1
,2
and3
.With a trivial number of distinct priority values, there is a simple solution. Create three partial indexes. All of them together are still smaller than your current indexes
idx_pd_order
oridx_pd_where
(which you might not be needing any more).Use this query:
This should be dynamite.
ORDER BY
clause in the outer query. In the current implementation, the order from the inner query is preserved as long as the outer query is as simple as that. To be sure, you could join right away (which may be a bit slower):.. or carry along
priority
andnext_execution_date
to order once more in the outer query (to be absolutely sure), which is probably slower, yet.All parentheses are needed! Related answer.
This query just reads tuples from the top of above partial indexes, no sort-step needed at all. All rows are pre-sorted, efficient to boot.
UNION ALL
queries without a finalORDER BY
can stop as soon the number of requested rows in the top-levelLIMIT
have been fetched. So if there are enough rows in the top priorities, subsequent legs of theUNION ALL
query are never executed. This way, only the smaller partial indexes have to be touched.JOIN
totb_st
later, should be more efficient.Again, the column
st_id
is only appended to the index in the hope for an index-only scan. If that works for you, the whole query does not even touch the tabletb_pd
at all.General solution for any number of distinct
priority
valuesWe have solved this before. There is a complete recipe to automate the creation of partial indexes and a function .. the works:
Can spatial index help a "range - order by - limit" query
Optimize table
Since you are trying to optimize performance and your table is big, I suggest a slightly altered layout for your table
tb_pd
:This occupies 52 bytes per row on disk, while your current design needs 60 bytes. Indexes profit as well. Details:
Configuring PostgreSQL for read performance
Of course, all the basic advice for performance-optimization applies as well.
About
"char"
: