Re: query plan with index having a btrim is different for strings of different length

>
> 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