Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id CB7BAB5D8C5 for ; Sat, 6 Aug 2011 00:09:51 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 89319-09 for ; Sat, 6 Aug 2011 03:09:45 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from nm10.bullet.mail.sp2.yahoo.com (nm10.bullet.mail.sp2.yahoo.com [98.139.91.80]) by mail.postgresql.org (Postfix) with SMTP id B21A8B5D800 for ; Sat, 6 Aug 2011 00:09:44 -0300 (ADT) Received: from [98.139.91.64] by nm10.bullet.mail.sp2.yahoo.com with NNFMP; 06 Aug 2011 03:09:43 -0000 Received: from [98.139.91.25] by tm4.bullet.mail.sp2.yahoo.com with NNFMP; 06 Aug 2011 03:09:43 -0000 Received: from [127.0.0.1] by omp1025.mail.sp2.yahoo.com with NNFMP; 06 Aug 2011 03:09:43 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 554894.87313.bm@omp1025.mail.sp2.yahoo.com Received: (qmail 35498 invoked by uid 60001); 6 Aug 2011 03:09:43 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1312600183; bh=roS3/9j6webM/i9e6w0BFDeFJgLE/XdnV+bOHIvHdBE=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=g1f8x2IJ9Nx99+QtRxQ4YVlzQSo9qKMiFDpl/0BOIBD2h2S3Tn6sPeVTWLIn+EsFakS4OTt9zbngF3GTGK+ntRSVL90JaGyQtjGrYkeR4XJrqwdzjCWbSkcK98nVUXXZWHhXVsqcdIe4MPjf76B2kYBO8ebg0q+cQa02dJiFvAk= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=wh+p/9ES9V5gtEsBtslL/IUf4Tbp28tUgd2zN/5zTDRfLWJqDo3j/oKmoBZG7xwNyonLHDTqkkqdRKYqryqkGzeIebZUnEkEpnMY+dbDnXbk+omPFPHOR4uK1R2p2huWK4bvPUPATcesw2SJZGZH34eEsdS1qtX7PeAk9KtOWmg=; X-YMail-OSG: DAIPxC0VM1kR3Ka4PPNBtUX9BMHLR6_T_r.GYfMf3up316N gcr4s9PZQvLkrrVwv2hzUhzvLJDokzco0rwAIcYH2LscEl1nF4iqqGa1QXOl HPzhQXkWhg9sSu3.5q0RRp06fVBldsWcfu1cQZ_fZ2VCiF2pPt6O8.hdgbCp XNstpvDXeJQntf_keeA9szYXlN2J23_g_tsOOD_jmhGvYL1qbSy1rUbV9UyF nMJmHkJq2ShUEkUfRRFQPF35mfh87FX.AfK8XB.kbHRIlWN361CeF0aQf_TZ U8Dz5CZZYtNwbmw8cwAxB8QMkickhcKYTLtX6TuGUA3qKuxxfD06EU5FAb2n ym3lb3qvdU9RttiJTaCrfX6mrUE2RMH.iOsZyjVQQVNmmyP_gL1qH0FkPmtA xiiCciI3OK45U17Wj6qFZsA-- Received: from [98.210.146.38] by web46113.mail.sp1.yahoo.com via HTTP; Fri, 05 Aug 2011 20:09:43 PDT X-Mailer: YahooMailWebService/0.8.113.313619 References: <4E3BE02B020000250003FB8A@gw.wicourts.gov> Message-ID: <1312600183.31294.YahooMailNeo@web46113.mail.sp1.yahoo.com> Date: Fri, 5 Aug 2011 20:09:43 -0700 (PDT) From: Greg Williamson Reply-To: Greg Williamson Subject: Re: PostgreSQL 9.0.1 on Windows performance tunning help please To: tuanhoanganh , Kevin Grittner Cc: pgsql-performance In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.579 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.1, FREEMAIL_FROM=0.001, FREEMAIL_REPLYTO_END_DIGIT=1.151, RCVD_IN_DNSWL_NONE=-0.0001, RP_MATCHES_RCVD=-0.931 X-Spam-Level: X-Archive-Number: 201108/100 X-Sequence-Number: 44547 Tuan --=0A=0A> =0A> Thanks for your help.=0A> I create index on channel_id = and data_id like your comment.=A0=0A...=0A<...>=0A> =0A> explain analyze se= lect d.data_id, d.table_name, d.event_type, d.row_data, d.pk_data, d.old_da= ta, d.create_time, d.trigger_hist_id, d.channel_id, d.transaction_id, > d.s= ource_node_id, d.external_data, '' from sym_data d inner join sym_data_gap = g on g.status=3D'GP' and d.data_id between g.start_id and g.end_id where=0A= > d.channel_id=3D'sale_transaction' order by d.data_id asc;=0A>=0A> Nested = Loop =A0(cost=3D0.00..1512979014.35 rows=3D26268463088 width=3D1401) (actua= l time=3D25741.704..7650979.311 rows=3D2764140 loops=3D1)=0A> =A0 -> =A0Ind= ex Scan using idx_d_channel_id3 on sym_data d =A0(cost=3D0.00..1781979.40 r= ows=3D3117384 width=3D1401) (actual time=3D83.718..55126.002 rows=3D3124631= loops=3D1)=0A> =A0 -> =A0Index Scan using sym_data_gap_pkey on sym_data_ga= p g =A0(cost=3D0.00..358.37 rows=3D8426 width=3D8) (actual time=3D2.428..2.= 429 rows=3D1 loops=3D3124631)=0A>=A0 =A0 =A0 =A0 Index Cond: ((d.data_id >= =3D g.start_id) AND (d.data_id <=3D g.end_id))=0A> =A0 =A0 =A0 =A0 Filter: = (g.status =3D 'GP'::bpchar)=0A> Total runtime: 7651803.073 ms=0A> =0A> But = query performance don't change.=0A> Please help me.=0A=0ADid you run an ana= lyze on the table after building the new indexes ? The row estimates seem t= o be off wildly,=0Aalthough that may be a symptom of something else and not= related, it is worth ruling out the easily tried.=0A=0AHTH,=0A=0AGreg Will= iamson=0A