1

I'm on the cusp of understanding this, but I don't understand the fix that I need. The following query takes about minute, give or take, to return 20 records:

explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" desC
LIMIT 20; 
                                                                           QUERY PLAN                                                                            
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=74.950..41059.055 rows=20 loops=1)
   Buffers: shared hit=38524709 dirtied=10
   ->  Index Scan Backward using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=74.947..41059.022 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10
 Planning time: 0.450 ms
 Execution time: 41059.233 ms

Flipping the sort order makes it take about 60ms:

explain (analyze, buffers)
select "search_docket"."id"
FROM "search_docket"
WHERE "search_docket"."court_id" = 'delctcompl'
ORDER BY "search_docket"."id" asC
LIMIT 20; 
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.56..2791.05 rows=20 width=4) (actual time=63.701..63.939 rows=20 loops=1)
   Buffers: shared hit=45783
   ->  Index Scan using search_docket_pkey on search_docket  (cost=0.56..8837737.95 rows=63342 width=4) (actual time=63.698..63.933 rows=20 loops=1)
         Filter: ((court_id)::text = 'delctcompl'::text)
         Rows Removed by Filter: 67080
         Buffers: shared hit=45783
 Planning time: 0.426 ms
 Execution time: 63.971 ms

You can see that in the first one, it gets nasty right here:

         Rows Removed by Filter: 41862720
         Buffers: shared hit=38524709 dirtied=10

Here are the indexes on the table (omitting ones that are not related to this query):

Indexes:
    "search_docket_pkey" PRIMARY KEY, btree (id)
    "search_docket_7a46e69c" btree (court_id)
    "search_docket_court_id_2d2438b2594e74ba_like" btree (court_id varchar_pattern_ops)

Stray thoughts:

  1. The court column has very low cardinality. It has about 500 values across about 50M rows.

  2. I guess I could add a descending index on court_id, and maybe that'd fix it, but that doesn't seem right.

  3. Maybe I need a multi-column index here on search_docket.id and search_docket.court_id? Seems off.

Is there something better I should be doing here that would make the index work better?

2
  • You make it seem like ascending or descending order are equally ok. If so, you can just remove the ORDER BY altogether and get arbitrary rows quickly with the plain index on (court_id). If not, specify which queries you run predominantly and their exact objective. And always disclose your version of Postgres, please. – Erwin Brandstetter Apr 27 at 15:49
  • Sorry, this is postgres 10, and this is for an API response, so we could flip the order of the API if that's the only way to get performance out of it, but it'd be disruptive. – mlissner Apr 27 at 18:22
3

Ideal for those queries would be in index on (court_id, id), and with the columns in that order. It should be extremely fast in either direction. And once you have it, you should be able to get rid of the plain index on court_id as it wouldn't be much good anymore.

2
  • I stared and stared at the query, and your answer, and this finally makes sense. Off to make that mega index. Here goes....thank you! – mlissner Apr 27 at 18:31
  • 1
    Tested and worked, thank you again for your help. – mlissner Apr 27 at 22:09
0

The rows that satisfy the condition (estimated to be around 63000) all have a high id. PostgreSQL does not know that, so it considers the index scan equally appealing in either direction.

If you know it is always like that, use DESC. If not, and you want to avoid the index scan on that index, change the ORDER BY clause so that it doea not match the indexed expression, for example by adding + 0.

2
  • So the speed depends on the value of the WHERE filter, which means I don't know if ordering acs or desc will be faster. Is there any way to fix the problem generally? I'm sort of surprised an index can be so slow. – mlissner Apr 27 at 14:31
  • You can use an index-only scan as jjanes' answer suggests. Or you can use an index on only court_id and use the trick with ORDER BY id + 0. The index-only scan will certainly be faster, but the index is larger. – Laurenz Albe Apr 27 at 15:03

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.