I have a query that is used VERY frequently in my application. Without modifying the query itself, I'm trying to optimize it by toying with different indexing schemes. Here's the query (basically):
SELECT foo from big_table, status_types WHERE big_table.attr=1 AND lower(big_table.groupname) LIKE lower('Bar%') AND big_table.type = status_types.typeid ORDER BY big_table.groupname DESC offset 0 limit 20; Limit (cost=24511.42..24511.45 rows=14 width=150) -> Sort (cost=24511.42..24511.45 rows=14 width=150) Sort Key: big_table.groupname -> Nested Loop (cost=0.00..24511.15 rows=14 width=150) -> Seq Scan on big_table (cost=0.00..24495.23 rows=14 width=72) Filter: ((big_table.attr = 1) AND (lower(groupname) ~~ 'Bar%'::text)) -> Index Scan using typeid on status_types (cost=0.00..1.12 rows=1 width=86) Index Cond: (status_types.typeid = big_table.type)
I have indicies on big_table for lower(groupname) and typeid is a PK for table status_types. I would think it should be using the index on big_table (on groupname) to avoid sorting the results. I have tried several different options, even changing the query to something as simple as "select * from big_table where groupname LIKE 'foo%' order by group_name". Still performs the sort!!
I must be doing something wrong. Shouldn't the query planner favor the use of the index and NOT sorting?