Re: Why Not MySQL?

> 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)

Thanks!.

> > 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?

ipa=> select count(app_id) from applicants_states;count
------
244367

ipa=> select count(app_id) from applicants;
count
-----9791

> > .... 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.

It's basically a search engine so yes, searching is FAR more frequently done
than inserts/updates/deletes

> Increase postmaster's -B and -S settings ...

I will. Thanks!

-Mitch Vincent