tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper, m_assignment WHERE m_object_paper.assignment = m_assignment.id AND m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND lower(btrim(x_firstname)) = lower(btrim('Jordan')) and lower(btrim(x_lastname)) = lower(btrim('Smith'));
Is there an index on "m_object_paper.assignment"? It could solve the problem.
With current indices on "btrim(last_name)" and "owner" you are just throwing the rows away (you have 521382 rows with "smith", 15494737 with owner=-1 and only 58 of them have both "smith"/"jordan" and -1).
Consider creating index on m_object_paper using btree(lower(btrim(x_lastname))) where owner=-1; (it might add firstname column there as per Tom's suggestion)
Or just index on (owner, lower(...)) if you have other queries with different values for owner.
One more point that could improve bitmap scans is greater value for work_mem. You'll need 8*15494737 ~ 130Mb == 130000 for work_mem (however, that is way too high unless you have lots of RAM and just couple of active database sessions)
Regards,
Vladimir Sitnikov
