[Solved] PostgreSQL 9.6 optimizer uses index even though it\'s slower

EverSQL Database Performance Knowledge Base

PostgreSQL 9.6 optimizer uses index even though it\'s slower

Database type:

I'm new to indexes and plans, so I had to ask for some help.

I have a table where I keep the following attributes:

id text NOT NULL 
t timestamp NOT NULL 
cost int NOT NULL

My query on the db is :

SELECT AVG(cost)
FROM my_table
WHERE my_to_char(t) = 'Sunday   '
;

The return time of the above query is about 4secs.
So, in order to increase query's performance I created an index on my_to_char(t).

But this proved to have the opposite result. The query now returns within 30 secs.

However, the query planner chooses to use the index rather than go for a sequential scan.

How can the index be slower than seq scan on the first place. Any explanation?

Maybe because there are too many rows with my_to_char(t) = 'Sunday '?

EDIT - Query Plan

Aggregate  (cost=72841.43..72841.44 rows=1 width=32) (actual time=28383.473..28383.473 rows=1 loops=1)
Output: avg(cost)
->  Bitmap Heap Scan on my_table  (cost=900.60..72732.77 rows=43462 width=4) (actual time=120.778..28091.814 rows=1237954 loops=1)
Output: id, t, cost
Recheck Cond: (my_to_char(my_table.t) = 'Sunday   '::text)
Rows Removed by Index Recheck: 3053757
Heap Blocks: exact=33988 lossy=26432
->  Bitmap Index Scan on btree_date  (cost=0.00..889.74 rows=43462 width=0) (actual time=111.785..111.785 rows=1237954 loops=1)
Planning time: 0.270 ms
Execution time: 28384.284 ms

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Calling Functions With Indexed Columns (query line: 6): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `t` is indexed, the index won’t be used as it’s wrapped with the function `my_to_char`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
The optimized query:
SELECT
        AVG(my_table.cost) 
    FROM
        my_table 
    WHERE
        my_to_char(my_table.t) = 'Sunday   '

Related Articles



* original question posted on StackOverflow here.