CODEWITHSUNDEEP
sqlpostgresqlindexingexplain
Dont_kill_me_pls
Dont_kill_me_pls

Reputation: 45

Postgres is not using partial index

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, 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

Upvotes: 1

Views: 432

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246568

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.

Upvotes: 2

Related Questions