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
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
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
SELECT AVG(my_table.cost) FROM my_table WHERE my_to_char(my_table.t) = 'Sunday '