Re: Turning off transactions completely. - Mailing list pgsql-general

> I would suggest to optimize your Application!

First thing I did! :-)

>   - cache previously inserted IDs and other things
>     (eg. use Perl hashes)

I'm using Java/JDBC. Also, there's very little data I can cache and reuse.

>
>   - create simpler Where clauses
>

Sorry, I need to be specific! Besides, I've used EXPLAIN extensively and
these are the fastest possible queries (atleast I hope so) which do what I
want them to do.

>   - look on your indexes, perhaps you can create an index on two columns?
>

Got them up the wazoo. Two column ones as well...

Just want to know, is an index on (foo,bar) different from (bar,foo)? Does
the order in which they appear in the index creation statement and in
subsequent queries make a difference?


> And:
>   use vacuum analyze after the Database is freshly build and filled
>   with the first ~ 100 000 rows.
>   Later vacuum analyze every 1 000 000 or 10 000 000 rows ...
>

I'm vacuuming like crazy (after the initial data COPY and then once the run
ends(to prepare for the next run)) but there are two issues...

1. VACUUM is dead slow. The CPU monitor shows long stretches when there's
barely any activity at all while the vacuum is going on.

2. I do vacuum analyze's to help the database figure out how to best run my
queries. But it *still* doesnt use some of them...

> > BTW, I've got a dual proc machine with a RAID-0 array and 1 GB of RAM,
but
> > pg only uses one CPU at a time. Would have been great if it had been
> > multi-threaded or something.
>
> if you use two inserting processes, Postgres also should use two. AFAIK!
:)
>

Ah, interesting point. I spent the last two (working) days converting my
single process app into a multi threaded one, to allow me to fire off
multiple queries at the same time. I was hoping this would lead to a more
optimum use of system resources... Didn't work. Best case, the
multi-threaded app is just as fast as the single -threaded one and in the
worst case; it's much slower.

I'm guessing that the various complex queries being fired at the same time
are forcing the use of Swap (yes, it on another HDD entirely) ; which is
slowing down the machine.

--Arsalan.