There is an order table which stores customer information and location. I need to filter the customer's last five locations within the last six months based on order status.
Query as follows,
SELECT *
FROM orders
WHERE ( status = 4
AND receiver_phone = '91xxxxxxx'
AND created_date BETWEEN '2017-08-06' AND '2017-03-01'
AND NOT ( receiver_latitude = '10.9315615'
AND receiver_latitude IS NOT NULL)
)
ORDER BY created_date DESC
limit 5;
EXPLAIN ANALYZE:
Limit (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.298..2151.298 rows=0 loops=1)
-> Sort (cost=374121.96..374121.97 rows=1 width=449) (actual time=2151.297..2151.297 rows=0 loops=1)
Sort Key: created_date DESC
Sort Method: quicksort Memory: 25kB
-> Seq Scan on orders (cost=0.00..374121.95 rows=1 width=449) (actual time=2151.262..2151.262 rows=0 loops=1)
Filter: ((created_date >= '2017-08-06 00:00:00+00'::timestamp with time zone) AND (created_date <= '2017-03-01 00:00:00+00'::timestamp with time zone) AND ((receiver_latitude <> 10.9315615) OR (receiver_latitude IS NULL)) AND (status = 4) AND ((receiver_phone)::text = '91xxxxxx'::text))
Rows Removed by Filter: 4376895
Planning time: 0.267 ms
Execution time: 2151.917 ms
what are best approaches to optimize this query?
Best Answer
Seq Scan means sequential Scanning, PostgreSQL enum have to enum all records for filtering result.
You should use Btree index. https://www.postgresql.org/docs/current/static/indexes-types.html