Functional Index LOWER PostgreSQL not working
Using citext

My suggestion is to move to the citext module.

CREATE EXTENSION citext;
BEGIN;
  --out with the old
  DROP INDEX fb_name_lower_alias;
  DROP INDEX fb_name_mid_lower_alias_index;
  DROP INDEX fb_name_alias;
  DROP INDEX fb_name_mid_alias_index;

  --in with the new
  ALTER TABLE fb_name
    ALTER COLUMN alias
    SET DATA TYPE citext;

  CREATE INDEX ON fb_name(mid,alias);
  CREATE INDEX ON fb_name(alias);
  ANALYZE fb_name;
COMMIT;


Then simply stop using lower() everywhere. Your query should like this,

SELECT *
FROM fb_name
INNER JOIN fb_kg ON mid = subject_mid
WHERE alias = 'Polaski'
  AND fb_kg.relation = 'location/location/containedby';


Everything else should work the same. Paste the EXPLAIN ANALYZE of the new query.

Update on indexes, and case-sensitive searching on citext

If  you need a case-sensitive query on a citext column, you just have to cast the other side to ::text, and in fact using the citext index is usually good enough. For example you can do a case-sensitive index only scan with WHERE col::citext = 'fOo'::citext AND col::text = 'fOo'; or, you could create two indexes on the citext column:


one that's case sensitive ON table(myCITEXT)
one that's case insensitive (I find that wasteful though) ON table(myCITEXT::text)