Covering index in Postgres 12

user_11668842 0
Viewed 50

My table has 5500000 test records (Postgres 12). I have an uncertainty because of big costs when I'm using a covering index on my tables.

So, I create index:

create index concurrently idx_log_type
    on logs (log_type)
    include (log_body);

After I execute a request:

explain (analyze)
select log_body
from logs where log_type = 1
limit 100;

I have getting a result:

Limit  (cost=0.43..5.05 rows=100 width=33) (actual time=0.118..0.138 rows=100 loops=1)
    - ->  Index Only Scan using idx_log_type on logs  (cost=0.43..125736.10 rows=2720667 width=33) (actual time=0.107..0.118 rows=100 loops=1)
        Index Cond: (log_type = 1)
        Heap Fetches: 0
Planning Time: 0.558 ms
Execution Time: 0.228 ms

At first glance, this is so good, but the cost range is very large. Is this normal or should you optimize?

indexing postgresql optimization
1 Answers

That's an artifact of EXPLAIN: it shows the cost for the complete index scan, just as if there were no LIMIT. But the whole plan is priced correctly.

So you can ignore the high upper cost limit on the index scan.

user_64643080
Related