Limit 1 is slower than limit 100

Question

I have a sql query

SELECT * FROM sharescheduledjob WHERE sharescheduledjob.status = 'SCHEDULED' ORDER BY id ASC LIMIT 1

with limit 1 it is very slow

Limit  (cost=0.43..46.43 rows=1 width=8) (actual time=3490.958..3490.959 rows=1 loops=1)
  ->  Index Scan using sharescheduledjob_pkey on sharescheduledjob sharesched0_  (cost=0.43..171383.41 rows=3726 width=8) (actual time=3490.956..3490.956 rows=1 loops=1)
        Filter: ((status)::text = 'SCHEDULED'::text)
        Rows Removed by Filter: 6058511
Total runtime: 3490.985 ms

But with limit 100 its pretty fast

Limit  (cost=248.04..248.29 rows=100 width=8) (actual time=12.968..12.994 rows=100 loops=1)
  ->  Sort  (cost=248.04..257.36 rows=3726 width=8) (actual time=12.966..12.978 rows=100 loops=1)
        Sort Key: id
        Sort Method: top-N heapsort  Memory: 29kB
        ->  Index Scan using sharescheduledjob_status on sharescheduledjob sharesched0_  (cost=0.43..105.64 rows=3726 width=8) (actual time=0.044..8.636 rows=9284 loops=1)
              Index Cond: ((status)::text = 'SCHEDULED'::text)
Total runtime: 13.042 ms

Is it possible to change database settings to enforce to lookup the status first?

I already tried

ANALYZE sharescheduledjob

but didnt help

And it is hard to change the query because its generated by hibernate from hql queries

Just a shot in the dark. Lowering random_page_cost can make it use the index faster. https://www.postgresql.org/docs/current/static/runtime-config-query.html#GUC-RANDOM-PAGE-COST — kometen, Dec 12 '17 at 11:29
`random_page_cost = 0.0` is a trerrible choice. IMHO you should not set it below `1.0` — joop, Dec 12 '17 at 11:47
Have you checked this answer? https://stackoverflow.com/a/27237698/319826 — kometen, Dec 12 '17 at 11:56

Limit 1 is slower than limit 100

0 Answers0