> Quick question: you are creating the indices *after* you insert the data,
> right?
That is correct.
> > 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...
>
> A vacuum analyze does a vacuum also so you only need to do the former.
Am aware of that. I only do 'analyzes'. Sorry if I didn't make that clear.
:-)
> Strange, that would indicate a serious bottleneck. Are you saturating the
> disks? You can use vmstat to work out the amount of disk activity.
Right. I guess I'll do that.
>
> > 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.
>
> You say you had 1 GB of memory? Could you give an example of a query that
> uses this amount of memory.
>
OKay....
When I create a double index on a table with ~3.3 GB of Data (size of the
initial plain text file). Course, on the second run, it'll have ~6.6 GB of
data... I haven't reached that far yet. I ran out of space on my array on
the first run itself. BTW, that's production data. For testing I use a 300
MB file. The sort mem is set so...
-S 350000
So that could be the reason. I don't mind the mem being used in the
single-threaded app (there's no one else there), but with multi-threads, it
could be a problem...
--Arsalan
