How to optimize query with ORDER and LIMIT? I have simple query:

SELECT *
FROM "reviews"
WHERE (reviews.branch_id = '12345678')
    AND (reviews.provider IN('first', 'second'))
    AND (is_hidden = FALSE)
ORDER BY reviews.date_created DESC
LIMIT 13


Table structure:

CREATE TABLE reviews (
    id bigint NOT NULL,
    provider character varying(32) NOT NULL,
    branch_id bigint NOT NULL,
    text text NOT NULL,
    date_created timestamp with time zone DEFAULT now() NOT NULL,
);
CREATE INDEX reviews_provider_branch_id_date_created_idx ON reviews(provider, branch_id, date_created DESC) WHERE is_hidden=false;


Explain:

Limit  (cost=3278.96..3278.99 rows=13 width=1183) (actual time=15.657..15.668 rows=13 loops=1)
Buffers: shared hit=1834
-&gt;  Sort  (cost=3278.96..3283.32 rows=1743 width=1183) (actual time=15.654..15.665 rows=13 loops=1)
    Sort Key: date_created DESC
    Sort Method: top-N heapsort  Memory: 41kB
    Buffers: shared hit=1834
    -&gt;  Index Scan using reviews_provider_branch_id_date_created_idx on reviews  (cost=0.43..3238.00 rows=1743 width=1183) (actual time=0.082..13.388 rows=1879 loops=1)
          Index Cond: (((provider)::text = ANY ('{first,second}'::text[])) AND (branch_id = '12345678'::bigint))
          Buffers: shared hit=1834
Planning time: 0.548 ms
Execution time: 15.791 ms


Without ORDER:

Limit  (cost=0.43..24.58 rows=13 width=1183) (actual time=0.047..0.088 rows=13 loops=1)
Buffers: shared hit=11
-&gt;  Index Scan using reviews_provider_branch_id_date_created_idx on reviews  (cost=0.43..3238.00 rows=1743 width=1183) (actual time=0.046..0.086 rows=13 loops=1)
    Index Cond: (((provider)::text = ANY ('{first,second}'::text[])) AND (branch_id = '12345678'::bigint))
    Buffers: shared hit=11
Planning time: 0.387 ms
Execution time: 0.135 ms


Is there any way to speed up this query?
While the records under branch_id = '12345678' and each of the two providers are sorted in the order you want, they aren't in a sorted order once they've been pulled out (because you've got two sets that are pushed together).

You might get an improved sort if you pulled the top 13 records for the branch_id and each of the providers separately. These should already be in the order you want. Then, UNION ALL the two result sets and grab the top thirteen from that. Since they're already sorted in the right order, the engine may realize that and do something faster than a HEAP SORT.

NOTE: Depending on the total number of rows involved, the simple expedient of grabbing the top 13 rows for each branch and provider may speed things up tremendously, in that the heap sort has to sort through all the possible matches to find the top 13, whereas we've limited the possible row count to 26 at most.

Of course, this doesn't scale well. If the number of branches and providers in the query is ultimately variable, then you would either need to write a new version of the query each time the number of either of those changed, or build some really hairy dynamic SQL. If that's the case, then trying to further limit the pool of candidate rows should help; consider putting some sort of date range into the query.

UPDATE: joanolo kindly put together the actual code for this, with a data sample of 900 records; here's the dbfiddle.uk link. It confirmed that the engine did pick something other than a heap sort - specifically, a quicksort. However, as joanolo notes, you need a reasonably large number of records for this to make a difference; with 900, it's faster, but not that much.