sql_performance_issue_limit_approach

limit_approach

SQL and Query Plan for the LIMIT approach (Cannot search for multiple author_ids)

SQL:
EXPLAIN ANALYZE
SELECT released, author_id
     FROM book
     	 WHERE author_id = '1'
	 AND released<=to_timestamp(2e9)
	 AND released>=to_timestamp(0)
	 ORDER BY released desc
         LIMIT 1

Query plan:
"Limit  (cost=0.43..1.55 rows=1 width=10) (actual time=0.112..0.113 rows=1 loops=1)"
"  ->  Index Only Scan Backward using book_idx1 on book  (cost=0.43..117791.74 rows=105146 width=10) (actual time=0.109..0.109 rows=1 loops=1)"
"        Index Cond: ((author_id = '1'::text) AND (released <= '2033-05-18 06:33:20+03'::timestamp with time zone) AND (released >= '1970-01-01 02:00:00+02'::timestamp with time zone))"
"        Heap Fetches: 1"
"Planning time: 0.446 ms"
"Execution time: 0.168 ms"