CODEWITHSUNDEEP
postgresqlquery-performancedatabase-indexes
Светлана Гоманюк
Светлана Гоманюк

Reputation: 11

How can I force PostgreSQL to use a certain index?

I have two Postgres indexes on my table cache, both on jsonb column on fields date and condition.

The first one works on an immutable function, which takes the text field and transforms it into the date type.

The second one is created only on text.

So, when I tried the second one, it turns my btree index into a bitmap index and somehow works slower than the first one, which takes another two steps to work, but use only an index scan.

I have two questions: why and how?

Why does the first one use only index, compared with the second, which for some reason uses a bitmap? And how I can force PostgreSQL to use only the index and no the bitmap on the second index, because I don't want use the function.

If there another solution then please give me hints, because I don't have permission to install packages on the server.

Function index:

create index cache_ymd_index on cache (
   to_yyyymmdd_date(((data -> 'Info'::text) ->> 'Date'::text)::character varying),
   ((data -> 'Info'::text) ->> 'Condition'::text)
)  where (((data -> 'Info'::text) ->> 'Condition'::text) = '3'::text);

Text index:

create index cache_data_index on cache (
   ((data -> 'Info'::text) ->> 'Date'::text),
   ((data -> 'Info'::text) ->> 'Condition'::text)
) where (((data -> 'Info'::text) ->> 'Condition'::text) = '3'::text);

The function itself:

create or replace function to_yyyymmdd_date(the_date character varying) returns date
    immutable language sql
as
$$
select to_date(the_date, 'YYYY-MM-DD')
$$;

ANALYZE condition for function index:

Index Scan using cache_ymd_index on cache  (cost=0.29..1422.43 rows=364 width=585) (actual time=0.065..66.842 rows=71634 loops=1)
  Index Cond: ((to_yyyymmdd_date((((data -> 'Info'::text) ->> 'Date'::text))::character varying) >= '2018-01-01'::date) AND (to_yyyymmdd_date((((data -> 'Info'::text) ->> 'Date'::text))::character varying) <= '2020-12-01'::date))
Planning Time: 0.917 ms
Execution Time: 70.464 ms

ANALYZE condition for text index:

Bitmap Heap Scan on cache  (cost=12.15..1387.51 rows=364 width=585)    (actual time=53.794..87.802 rows=71634 loops=1)
  Recheck Cond: ((((data -> 'Info'::text) ->> 'Date'::text) >= '2018-01-01'::text) AND (((data -> 'Info'::text) ->> 'Date'::text) <= '2020-12-01'::text) AND (((data -> 'Info'::text) ->> 'Condition'::text) = '3'::text))
  Heap Blocks: exact=16465
  ->  Bitmap Index Scan on cache_data_index  (cost=0.00..12.06 rows=364 width=0) (actual time=51.216..51.216 rows=71634 loops=1)
        Index Cond: ((((data -> 'Info'::text) ->> 'Date'::text) >= '2018-01-01'::text) AND (((data -> 'Info'::text) ->> 'Date'::text) <= '2020-12-01'::text))
Planning Time: 0.247 ms
Execution Time: 90.586 ms

Upvotes: 0

Views: 4144

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247950

A “bitmap index scan” is also an index scan. It is what PostgreSQL typically chooses if a bigger percentage of the table blocks have to be visited, because it is more efficient in that case.

For an index range scan like in your case, there are two possible explanations for this:

  1. ANALYZE has run between the two indexes have been created, so that PostgreSQL knows about the distribution of the indexed values in the one case, but no the other.

    To figure out if that was the case, run

    ANALYZE cache;

    and then try the two statements again. Maybe the plans are more similar now.

  2. The statements were run on two different tables, which contain the same data, but they are physically arranged in a different way, so that the correlation is good on the one, but bad on the other. If the correlation is close to 1 or -1, and index scan becomes cheaper. Otherwise, a bitmap index scan is the best way.

    Since you indicate that it is the same table in both cases, this explanation can be ruled out.

The second column of your index is superfluous; you should just omit it. Otherwise, your two indexes should work about the same.

Of course all that would work much better if the table was defined with a date column in the first place...

Upvotes: 2

Related Questions