Re: Why the difference in plans ?

Dave,
>      "user_profile_pkey" PRIMARY KEY, btree (uid) CLUSTER
>      "user_profile_name_idx" UNIQUE, btree (name varchar_pattern_ops)
>      "user_profile_name_key" UNIQUE, btree (name)
>      "user_profile_uploadcode_key" UNIQUE, btree (uploadcode)
>      "user_profile_active_idx" btree (isactive)
>      "user_profile_areacode_index" btree (areacode)
>      "user_profile_gender_idx" btree (gender)

You need to change one of the name indexes to a functional index on
lower(firstname).  That'll speed the query up considerably.

I'm still puzzled as to why the index is being used at all in the 2nd
query, as it seems very unlikely to work out, but the above is the
practical solution to your problem.


--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco