X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C68E55387F for ; Thu, 21 Apr 2005 12:14:30 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 06208-01 for ; Thu, 21 Apr 2005 15:14:25 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 40C5753A85 for ; Thu, 21 Apr 2005 12:14:24 -0300 (ADT) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j3LFELSY008330; (envelope-from ) Thu, 21 Apr 2005 10:14:21 -0500 (CDT) Received: from [192.168.1.11] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j3LFEJGV015541 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Thu, 21 Apr 2005 10:14:20 -0500 (CDT) Message-ID: <4267C34D.9010705@arbash-meinel.com> Date: Thu, 21 Apr 2005 10:14:21 -0500 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Shoaib Burq (VPAC)" Cc: Russell Smith , Jeff , pgsql-performance@postgresql.org Subject: Re: two queries and dual cpu (perplexed) References: In-Reply-To: X-Enigmail-Version: 0.91.0.0 Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig46BEEC8F536CA754616F5660" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/569 X-Sequence-Number: 11966 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig46BEEC8F536CA754616F5660 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Shoaib Burq (VPAC) wrote: >Just tried it with the following changes: > >shared_buffers = 10600 >work_mem = 102400 >enable_seqscan = false > >still no improvement > >Ok here's the Plan with the enable_seqscan = false: >ausclimate=# explain ANALYZE select count(*) from "getfutureausclimate"; > > Actually, you probably don't want enable_seqscan=off, you should try: SET enable_nestloop TO off. The problem is that it is estimating there will only be 44 rows, but in reality there are 13M rows. It almost definitely should be doing a seqscan with a sort and merge join. Also, please attach you explain analyzes, the wrapping is really hard to read. I don't understand how postgres could get the number of rows that wrong. It seems to be misestimating the number of entries in IX_ClimateId Here: -> Index Scan using "PK_Aus40_DEM" on "Aus40_DEM" (cost=0.00..6.01 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=13276368) Index Cond: ("outer"."AusPosNumber" = "Aus40_DEM"."AusPosNumber") -> Index Scan using "PK_CurrentAusClimate" on "CurrentAusClimate" (cost=0.00..46.20 rows=11 width=14) (actual time=0.007..0.009 rows=1 loops=13276368) The first index scan is costing you 0.006*13276368=79s, and the second one is 119s. I can't figure out exactly what is where from the formatting, but the query that seems misestimated is: -> Index Scan using "IX_ClimateId" on "ClimateChangeModel40" (cost=0.00..1063711.75 rows=265528 width=20) (actual time=28.311..17212.703 rows=13276368 loops=1) Index Cond: ("outer"."ClimateId" = "ClimateChangeModel40"."ClimateId") Is there an unexpected correlaction between ClimateChangeModel40"."ClimateId" and whatever "outer" is at this point? John =:-> --------------enig46BEEC8F536CA754616F5660 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCZ8NNJdeBCYSNAAMRAgS+AJ9vt/eV0zOXA5DYFs5J0bPayng+BwCfQKCJ /+LRzkIYqSX5C0U6kuLya7c= =+Ufz -----END PGP SIGNATURE----- --------------enig46BEEC8F536CA754616F5660--