Re: Why Not MySQL?

On Wed, 3 May 2000, Mitch Vincent wrote:

> Here are some typical queries my application might generate. Please, let me
> know if you see anything that can be improved!

First comment ... Tom Lane always jumps on me on this ... if you are going
to send a QUERY to get recommendations on, send in an EXPLAIN on that
query also, so that we can see what the backend 'thinks" its going to do
...

> select * from applicants as a where a.created::date = '05-01-2000' and
> a.firstname ~* '^mitch' limit 10 offset 0

First comment, that Tom can clarify in case I'm wrong ... when I ran
UDMSearch under v6.5.3, there was a problem where a LIKE query was causing
a query to take forever to complete ... Tom, at the time, got me to change
the query so that instead of:

url LIKE '%s'

it was:

(url || '') LIKE '%s'

Now, this was in an earlier RC of v7.0 that I had to do this, and Tom made
some changes to the following one to 'fix the problem', but my performance
went from several *minutes* to several *seconds* of time to complete the
exact same query ...

> > The usual way to deal wih them is tuning your db structure and/or
> > queries or
> > setting backend options to use more memory for stuff or other such
> > things.
>
> 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!

on my machine (FreeBSD 4.0-STABLE), I'm currently running with a kernel
of:

options SYSVSHM
options SHMMAXPGS=262144
options SHMSEG=32

options SYSVSEM
options SEMMNI=40
options SEMMNS=240
options SEMMNU=120
options SEMMAP=120

options SYSVMSG

and a -B set to 4096 and -o ' -S 16384 ' ... the -B deals with teh amoun
tof shared memory, the -S I'm using only affects stuff like ORDER BY and
GROUP BY (allocates up to how much RAM to use on a sort before going to
disk ...

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org secondary: scrappy(at){freebsd|postgresql}.org