On Wed, 15 Nov 2006 21:47:49 -0500 Tom Allison <[EMAIL PROTECTED]> wrote:
> Believe me, I'm not trying to be an expert here, so if I'm wrong I
> could benefit from some explaining why... but...
> instead of:
>
> SELECT user_idnr FROM dbmail_users WHERE lower(userid) =
> lower('[EMAIL PROTECTED]')
>
> Wouldn't it make more sense to create these entries with a INSERT
> INTO .. lower() so that you can take advantage of a userid index?
>
> dbmail_users_name_idx" UNIQUE, btree (userid)
>
>
>
> dbmail=> explain SELECT user_idnr FROM dbmail_users WHERE
> lower(userid) = lower('[EMAIL PROTECTED]');
> QUERY PLAN
> ------------------------------------------------------------------
> Seq Scan on dbmail_users (cost=0.00..195.09 rows=1 width=8)
> Filter: (lower((userid)::text) = '[EMAIL PROTECTED]'::text)
> (2 rows)
>
> dbmail=> explain SELECT user_idnr FROM dbmail_users WHERE userid =
> '[EMAIL PROTECTED]';
> QUERY PLAN
>
> -------------------------------------------------------------------------------------
> Bitmap Heap Scan on dbmail_users (cost=57.00..61.01 rows=1 width=8)
> Recheck Cond: ((userid)::text = '[EMAIL PROTECTED]'::text)
> -> Bitmap Index Scan on dbmail_users_name_idx (cost=0.00..57.00
> rows=1 width=0)
> Index Cond: ((userid)::text = '[EMAIL PROTECTED]'::text)
> (4 rows)
> _______________________________________________
> DBmail mailing list
> [email protected]
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
In postgres, it's possible to create an index based on a function:
CREATE INDEX lower_userid ON dbmail_users (lower(userid));
At that point the query would take about the same amount of time and
userids can stay in different cases.
Jason
signature.asc
Description: PGP signature
