From: | "Mitch Vincent" <mitch(at)huntsvilleal(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Why Not MySQL? |
Date: | 2000-05-03 17:44:21 |
Message-ID: | 039a01bfb527$36d146a0$4100000a@venux.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2000-05-03 17:53:37 | Re: Why Not MySQL? |
Previous Message | Tom Lane | 2000-05-03 17:38:56 | Re: 7.0RC2 compile error ! |