Re: Why Not MySQL? - Mailing list pgsql-hackers

I was just playing with some of the suggested lower() indexes and ran into a
bit of trouble, it's no doubt from my lack of understanding but still, I
wanted to ask..

Do dum :

ipa=# create index applicants_firstname on applicants(lower(firstname));
ERROR:  DefineIndex: function 'lower(varchar)' does not exist

...that syntax is right, isn't it?

Thanks!

- Mitch

----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mitch Vincent <mitch@huntsvilleal.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Wednesday, May 03, 2000 1:31 PM
Subject: Re: [HACKERS] Why Not MySQL?


> "Mitch Vincent" <mitch@huntsvilleal.com> writes:
> > Here are some typical queries my application might generate. Please, let
me
> > know if you see anything that can be improved!
>
> > select * from applicants as a where a.created::date = '05-01-2000' and
> > a.firstname ~* '^mitch' limit 10 offset 0
>
> Neither of these WHERE clauses can be used with a plain-vanilla index
> (I'm assuming a.created is of time datetime?), so you're getting a
> simple sequential scan over the whole table --- unless the LIMIT stops
> it sooner.  If the table is large then you could get better performance
> by arranging for an indexscan using whichever clause is likely to be
> more selective (I'd be inclined to go for the date, I think, unless your
> creation dates come in bunches).
>
> The trick for the date test would be to have a functional index on
> date(a.created).  I'm not sure how bright 6.5.* is about this, but
> it definitely works in 7.0:
>
> create table foo (f1 datetime);
>
> -- a straight index on f1 is no help:
> create index foof1 on foo(f1);
> explain select * from foo where f1::date = '05-01-2000';
> NOTICE:  QUERY PLAN:
>
> Seq Scan on foo  (cost=0.00..25.00 rows=10 width=8)
>
> -- but an index on date(f1) is:
> create index foof1date on foo(date(f1));
> explain select * from foo where f1::date = '05-01-2000';
> NOTICE:  QUERY PLAN:
>
> Index Scan using foof1date on foo  (cost=0.00..8.16 rows=10 width=8)
>
> If you wanted to make the scan on firstname indexable, you'd need to
> make an index on lower(firstname) and then change the query to read
> ... lower(a.firstname) ~ '^mitch'
> or possibly
> ... lower(a.firstname) ~ lower('^mitch')
> if you don't want to assume the given pattern is lowercase to begin
> with.  (The second example will fail to be indexed under 6.5, but should
> be just fine in 7.0.)  ~* can't use an index under any circumstance,
> but ~ can if the pattern has a left-anchored fixed prefix.
>
> > select * from applicants as a,applicants_states as s where a.firstname
~*
> > '^mitch' and s.rstate='AL' and s.app_id=a.app_id limit 10 offset 0
>
> Again, the ~* clause is not indexable as-is, but the rstate clause
> would be if you have an index on s.rstate --- however, I imagine that
> it wouldn't be very selective, either, so it might not be worth the
> trouble.  Changing the query to make the firstname part be indexable
> could be a win.  You also need to look at how the join between a and s
> is being done.  How big are these tables, anyway?
>
> > .... There are 63 fields in the 'applicants' table, all of which are
> > searchable. Would it be a good or bad thing to index all fields that are
> > searchable?
>
> A lot of indexes will hurt your insert/update/delete times, so I
> wouldn't recommend having a whole bunch of indexes unless searches are
> far more frequent than changes.  What you want is a few well-chosen
> indexes that match the commonly used kinds of WHERE clauses in your
> query mix.
>
> > I'd love some pointers!  This machine has lots-n-lots of memory. I'd
love to
> > make postgre use more than normal if it would get me better speed!
>
> Increase postmaster's -B and -S settings ...
>
> regards, tom lane
>