search

ui / django-post_office

A Django app that allows you to send email asynchronously in Django. Supports HTML email, database backed templates and logging.
MIT License
1k stars 270 forks source link

optimize get_queue query #446

Closed marsha97 closed 1 year ago

marsha97 commented 1 year ago

OR query somehow not using index condition in our server, resulting in a very slow query(even for .exists()) but changing it to IN improve the speed pretty much this is for comparison:

Using status OR

                                                                                                                              QUERY PLAN                                                                                                                                
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..6.41 rows=1 width=4) (actual time=20346.830..20346.831 rows=0 loops=1)
   ->  Seq Scan on post_office_email  (cost=0.00..1666614.04 rows=259811 width=4) (actual time=20346.829..20346.829 rows=0 loops=1)
         Filter: (((scheduled_time <= '2023-08-07 03:49:47.800747+00'::timestamp with time zone) OR (scheduled_time IS NULL)) AND ((expires_at > '2023-08-07 03:49:47.800747+00'::timestamp with time zone) OR (expires_at IS NULL)) AND ((status = 2) OR (status = 3)))
         Rows Removed by Filter: 5747818
 Planning Time: 0.110 ms
 Execution Time: 20346.850 ms
(6 rows)

using status IN

                                                                                                              QUERY PLAN                                                                                                              
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..1.03 rows=1 width=4) (actual time=1.231..1.231 rows=1 loops=1)
   ->  Index Scan using post_office_email_status_013a896c on post_office_email  (cost=0.43..154141.61 rows=259811 width=4) (actual time=1.230..1.230 rows=1 loops=1)
         Index Cond: (status = ANY ('{2,3}'::integer[]))
         Filter: (((scheduled_time <= '2023-08-07 03:49:47.800747+00'::timestamp with time zone) OR (scheduled_time IS NULL)) AND ((expires_at > '2023-08-07 03:49:47.800747+00'::timestamp with time zone) OR (expires_at IS NULL)))
 Planning Time: 0.130 ms
 Execution Time: 1.250 ms
(6 rows)