From: Emanuel Calvo Franco <postgres.arg@gmail.com>
To: pgsql-admin@postgresql.org
Subject: Re: very, very slow performance
Date: Fri, 20 Feb 2009 10:10:12 -0200
Message-ID: <f205bb120902200410p2a2ac249m13c13f1bf2c96c9b@mail.gmail.com> (raw)
In-Reply-To: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F760@egcrc-ex01.egcrc.org>
References: <FE44E0D7EAD2ED4BB2165071DB8E328C0378F760@egcrc-ex01.egcrc.org>
2009/2/20 Tena Sakai <tsakai@gallo.ucsf.edu>:
> Hi Everybody,
>
> Here's the query (please read them in fixed-size font, if
> you can):
>
> select subjectid, genotype.markerid, a1.value as allele1,
> a2.value as allele2, genotype.dateCreated,
> genotype.dateReplaced, genotype.ignore,
> genotype.inconsistent
> from genotype, allele a1, allele a2
> where
> allele1id = a1.alleleid
> and
> allele2id = a2.alleleid;
>
> Genotype table mentioned above has about 600,000,000+ rows. As
> I mentioned, there are 20 more of them running concurrently.
> 3 other jobs look like:
>
Do you tried partitioned tables? diferent tablespaces? set the storage
external for
more important columns? what kind of indexes do you have?
> SELECT a.markerid,a.type,a.localname,b.ncbibuild,
> a.chromosome,a.geneticposition,b.physicalposition,
> a.strand,a.stdflanks,a.maxflanks,a.datecreated,
> a.datereplaced,a.sourcetablename,a.sourceid,
> b.dbsnprsid,a.ignore,a.gene
> FROM public.marker a, public.snpposition b
> WHERE
> a.ignore= 'N'
> AND a.datecreated <= (timestamp'Wed Oct 29 09:35:54.266 2008')
> AND a.datereplaced > (timestamp'Wed Oct 29 09:35:54.266 2008')
> AND a.localname IN
> ('RS10757474','RS7859598','RS6148','RS9792663','RS1541125',
> 'RS10511446','RS10814410','RS12338622','RS875587',
> 'RS1590979', 'RS748786','RS958505','RS12352961',
> and on and on and on...);
>
>
> insert into summarystats
> select 'Marker by Chromosomes', chromosome,
> sourcetablename,
> count(*), null, to_timestamp('2009-02-18
> 20:29:40.125',
> 'yyyy-mm-dd hh:mi:ss.ms')
> from marker
> where ignore = 'N'
> and datereplaced = '3000-01-01 12:00:00.000'
> and exists (select 1
> from genotype
> where genotype.markerid =
> marker.markerid
> and genotype.ignore = 'N'
> and genotype.datereplaced =
> '3000-01-01 12:00:00.000')
> group by chromosome, sourcetablename;
>
> COPY public.genotype (genotypeid, subjectid, markerid,
> allele1id, allele2id, datecreated, datereplaced,
> ignore, inconsistent, sourcetablename, sourceid)
> TO stdout;
>
Like Scott saids, try to run explain analyze for those querys, then
post the results.
>
> Can anybody suggest anything that I can do to gain speed?
> Any help is much appreciated.
>
I recommend follow this thread:
http://archives.postgresql.org/pgsql-hackers/2009-02/msg00718.php> Regards,
>
> Tena Sakai
> tsakai@gallo.ucsf.edu
>
>
>
--
Emanuel Calvo Franco
Sumate al ARPUG !
(www.postgres-arg.org -
www.arpug.com.ar)
ArPUG / AOSUG Member
Postgresql Support & Admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: postgres.arg@gmail.com
Subject: Re: very, very slow performance
In-Reply-To: <f205bb120902200410p2a2ac249m13c13f1bf2c96c9b@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox