https://prefect.io logo
Powered by
Title
j

Jons Cyriac

01/27/2023, 8:11 AM
Hi! can we use BRIN index for timestamp instead of btree in log table? when the table gets huge, queries are getting real slow.
p

Peyton Runyan

01/28/2023, 12:40 AM
What's your take on why a BRIN index would improve this?
j

Jons Cyriac

01/31/2023, 4:43 AM
from the docs 
BRIN stands for Block Range Index. BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table.
since logs are usually appended to storage, i think this would be a good fir for the index. I have replaced the btree index on timestamp and used BRIN instead in the log table having around 40 million records. before it used to time out everytime. And with BRIN, query response is well below a second.
p

Peyton Runyan

01/31/2023, 1:53 PM
Interesting that you're seeing such a performance difference. My understanding of BRIN is that it tends to result in a much smaller index, so you get size savings, but often you get a penalty on queries until you're doing very large selects since it points to page offsets. What does your query look like?
*And to answer your original question - you can absolutely set your own indices. As long as the name doesn't conflict with a prefect index name, you're all set
👍 1
j

Jons Cyriac

02/01/2023, 9:49 AM
SELECT log.id, log.created, log.updated, log.name, log.level, log.flow_run_id, log.task_run_id, log.message, log.timestamp 
FROM log 
WHERE log.level >= 1::integer AND log.flow_run_id IN ('5f0ee065-7e29-41d4-8513-1327b186ddd8'::uuid) ORDER BY log.timestamp DESC 
 LIMIT 500::integer OFFSET 0::integer
:thank-you: 1
#prefect-communityJoin Slack
Powered by