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@hub.org secondary: scrappy@{freebsd|postgresql}.org
