Postgres is not using partial index

Question

I tried to count id in a table using partial index, but performance very low

SELECT COUNT("refuel_request"."refuel_request_id") as "count" 
FROM "refuel_request" 
WHERE "refuel_request"."refuel_request_status_id" 
IN ('1','2','3')

I am created index

CREATE INDEX idx_refuel_request_status_id10 ON refuel_request (refuel_request_status_id)
WHERE "refuel_request"."refuel_request_status_id" 
IN ('1','2','3')

Can you explain what do I do wrong?

Explain

[
 {
"Plan": {
  "Node Type": "Seq Scan",
  "Parallel Aware": false,
  "Relation Name": "refuel_request",
  "Alias": "refuel_request",
  "Startup Cost": 0,
  "Total Cost": 160442.88,
  "Plan Rows": 4700100,
  "Plan Width": 16,
  "Filter": "(refuel_request_status_id = ANY ('{1,2,3}'))"
   }
  }
 ]

EXPLAIN (ANALYZE, BUFFERS)

explain (analyze, format text)

Finalize Aggregate  (cost=114931.68..114931.69 rows=1 width=8) (actual time=570.019..570.020 rows=1 loops=1)
  ->  Gather  (cost=114931.47..114931.68 rows=2 width=8) (actual time=569.735..575.504 rows=3 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial Aggregate  (cost=113931.47..113931.48 rows=1 idth=8) (actual time=528.094..528.094 rows=1 loops=3)
              ->  Parallel Seq Scan on refuel_request cost=0.00..109035.53 rows=1958375 width=16) (actual time=0.070..452.908 rows=1566700 loops=3)
                    Filter: (refuel_request_status_id = ANY ('{1,2,3}'))
Planning Time: 0.665 ms
Execution Time: 575.538 ms

Maybe you table is to small - planner choose seq scan in parallel beceuse this method have lover costs to do this query for your table now. Other option is - your clause in cover almost whole table.... — areklipno
– areklipno, Commented Mar 31, 2021 at 6:06
check same query when index is not partial - if plan will be without index and if you have only 3 values in condition - maybe query with union all will use non partial index .... — areklipno
– areklipno, Commented Mar 31, 2021 at 6:27
@areklipno I am tried to use CREATE INDEX idx_refuel_request_status_id ON refuel_request (refuel_request_status_id) . But it is not working — Dont_kill_me_pls
– Dont_kill_me_pls, Commented Mar 31, 2021 at 6:35

Laurenz Albe · Accepted Answer · 2021-03-31 07:36:59Z

2

No rows are removed by the filter in the sequential scan, so it seems that all rows in the table have refuel_request_status_id equal to '1', '2' or '3'. So the WHERE condition in the index doesn't make a difference.

You could try with enable_seqscan = off to see if PostgreSQL made the right choice. If not, perhaps random_page_cost is not correctly set for your hardware.

If it turns out that the sequential scan is indeed the fastest way to go, there is nothing much you can do to speed up the query: you can get faster storage or more RAM to cache the table.

Counting is slow business.

edited Mar 31, 2021 at 7:36

answered Mar 31, 2021 at 7:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dont_kill_me_pls Over a year ago

enable_seqscan = off increases execution time to 4 second( Do you have any idea for update performance for this query?

Laurenz Albe Over a year ago

I was wrong about the index-only scan - see my updated answer.

Collectives™ on Stack Overflow

Postgres is not using partial index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related