CODEWITHSUNDEEP
postgresql
Kacy
Kacy

Reputation: 3430

Does ORDER BY affect multiple WHERE conditions?

I'm trying to implement paging on a table named posts, and I'm using the columns time_created and post_id as the delimiter for the pages.

It's possible that multiple posts are created at the exact same time, so when retrieving the next page, I need to get all the remaining posts created at a specific time, and then get posts created before that time.

My concern is that postgres doesn't care about the order of WHERE conditions, and since I have 2 conditions separated by an OR, the following query may only use the second condition to fulfill the LIMIT requirement.

My question is: Does the ORDER BY clause guarantee that the first condition I provided (time_created = $1 AND post_id < $2) will be executed first since time_created = $1 should logically come before time_created < $1 when ordered by descending order? 

SELECT * FROM posts 
WHERE (time_created = $1 AND post_id < $2) OR time_created < $1 
ORDER BY time_created DESC, post_id DESC
limit 50;

Upvotes: 0

Views: 134

Answers (1)

klin
klin

Reputation: 121604

The query will be executed in three steps:

  1. Eliminate rows which do not satisfy conditions in the WHERE clause.
  2. Order remaing rows by expression(s) in the ORDER BY clause.
  3. Get first 50 rows according to the LIMIT.

So the order does not affect filtering.

If you run EXPLAIN ANALYSE <your query> you will get something like this:

                                                                                        QUERY PLAN                                                                                         
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=430.71..430.84 rows=50 width=12) (actual time=1.727..1.730 rows=50 loops=1)
   ->  Sort  (cost=430.71..445.82 rows=6042 width=12) (actual time=1.726..1.727 rows=50 loops=1)
         Sort Key: time_created DESC, post_id DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on posts  (cost=0.00..230.00 rows=6042 width=12) (actual time=0.010..1.329 rows=6027 loops=1)
               Filter: (((time_created = '2018-07-01 00:00:00'::timestamp without time zone) AND (post_id < 1111)) OR (time_created < '2018-07-01 00:00:00'::timestamp without time zone))
               Rows Removed by Filter: 3973
 Planning time: 0.493 ms
 Execution time: 1.759 ms
(9 rows)

which can be interpreted as the three steps described above.

Upvotes: 1

Related Questions