Re: simultaneously reducing both memory usage and runtime for a query

On 03/28/2010 07:43 PM, Andy Colson wrote:
> On 03/28/2010 03:05 PM, Faheem Mitha wrote:
>>
>>
>
> Wait... Your saying your question is so complex it needs 41 pages
> (including graphs) to ask? I didn't bother before, but now I'm curious,
> I'll have to go take a look.
>
> -Andy

Faheem, you seem to be incredibly detail oriented. We probably on need 10% of whats in diag.pdf:

16 core, 64 gig ram, 6 drives on 3ware 9690SA-8I card in RAID 10, with slow read (280mb/sec) and write (40mb/sec).

Running 64 bit Debian lenny with postgresql 8.4.

config settings:

shared_buffers = 2GB
work_mem = 1GB
maintenance_work_mem = 8GB
wal_buffers = 16MB
checkpoint_segments = 50
effective_cache_size = 50GB

There is sql script to create the tables n'stuff.

A Few counts:

affy6_faheem=# select count(*) from anno;
count
--------
932979
(1 row)

affy6_faheem=# select count(*) from geno;
count
-----------
825733782
(1 row)

The rest is irc chat about getting the data imported into PG, and other than slowness problems, does not seem relevant to the sql in opt.pdf.

As for opt.pdf, I dont think, again, we need all that detail. And the important parts got cut off. The explain analyze output is needed, but its cut off.

I'd recommend you paste the output here:

http://explain.depesz.com/

And give us links. The explain analyze will have counts and info that we (ok not me, but Tom and others) can use to help you.

You also seem to have gone through several revisions of the sql (I admit, I just skimmed the pdf's), it would be great if you could drop the ones you are sure are not useful, and we concentrate on just one or two.

-Andy