As a followup, I find that I can avoid the seq scan by adding an index
to that table as:
create index mb_name_own_idx on mb ( lower(name), ownerid );
and changing the query from using the idiom:
WHERE name ILIKE 'foo@bar' AND ownerid=7;
to using:
WHERE lower(name) = lower('foo@bar') AND ownerid=7;
which saves 20+ ms on each of the 30+ k such selects in a full run.
I haven't tested how fast it would be with that change and a utf8 ctype.
Because of how the middleware achives its portability between pg, my et al,
changing it to use lower and = will require significant surgery.
Is there any way to specify the index such that the ILIKE query will use
said index?
-JimC
--
James Cloos <cl...@jhcloos.com> OpenPGP: 1024D/ED7DAEA6
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
