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

"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