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?
work_mem
evangelist, but I would recommend checking that parameter in your configuration. PG uses that setting to decide at what point it should overflow to a file to do its sorting. Sorting shouldn't be particularly expensive for data that fits inwork_mem
. – Daniel Lyons Feb 29 '12 at 5:55