At 09:00 PM 5/15/2002, Martijn van Oosterhout wrote:
>[Much snipped about mergejoins]
>
> > AND LOWER(a.email) = LOWER(b.email);
>
>There's your problem. You're not comparing the two columns, you're comparing
>the two columns after running through a function, so it can't use the index.
>
>Try creating an index on LOWER(email) instead of just email.
Thanks. It actually is, already. I noticed that the \d lower_email_idx
displays:
pexitest=# \d lower_email_idx
Index "lower_email_idx"
Column | Type
--------+------
lower | text
btree
But if I look at a pg_dump, you can see I covered this base already (I
indexed every column used in the majority of searches by turning on query
debug and then sort | uniq them, a few months ago):
-- Name: "lower_email_idx" Type: INDEX Owner: dfields
--
CREATE INDEX lower_email_idx ON list_entries USING btree (lower(email));
So - that's not the problem.
Although I did run a test on a new table, where I created an additional
column called lower_email and set it accordingly - and it does do the merge
join if you set enable_nestloop=off (but not if it is on).
However, I don't want to store the same data twice...
Other ideas, please?
Cheers,
Doug
