0

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?

2
  • Unrelated, but AND receiver_latitude IS NOT NULL is useless as receiver_latitude = '10.9315615' already implies NOT NULL. That condition also doesn't match the execution plan (where it states: receiver_latitude <> 10.9315615) Sep 6, 2017 at 10:45
  • Also, the order of operands of BETWEEN is first AND last. The other way around it won't work.
    – dezso
    Sep 6, 2017 at 10:50

1 Answer 1

-1

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

B-trees can handle equality and range queries on data that can be sorted into some ordering

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.