Re: Force a merge join? - Mailing list pgsql-general

From Doug Fields
Subject Re: Force a merge join?
Date
Msg-id 5.1.0.14.2.20020516014610.02f2a920@mail.pexicom.com
Whole thread Raw
In response to Re: Force a merge join?  (Martijn van Oosterhout <kleptog@svana.org>)
List pgsql-general
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



pgsql-general by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: Should be easy enough to get this result (or is it
Next
From: Karel Zak
Date:
Subject: Re: XML from postgreSQL tables