Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 1F37B9FB58A for ; Fri, 2 Mar 2007 11:28:22 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 90183-06 for ; Fri, 2 Mar 2007 11:28:02 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.239]) by postgresql.org (Postfix) with ESMTP id 33F779FB38F for ; Fri, 2 Mar 2007 11:28:14 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so816051wxd for ; Fri, 02 Mar 2007 07:28:12 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=MQsgkOwhtxdfaLSwqm/VQ3yymphwbYaf91cNBvcfeFiQp5/OKPD5N2fsOaqJzSvDwRHlBNt86g9DAZ5gEYxYCikJ60A1XWeeMiVu4wiGSaId2NREtjPV0vTR3MBU4MlIl7G/xCjpLKaTyMZy4e+dCrY/3zRtgA5TVPdrULAZcb4= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=BcQo8W441aPMmpQ64S6UM4A4AdzpKKHAJLKKrxxiF3lfDbjYA64fpIn/HY3GqKXGVNLpkH9msRVmLvxGdZ/5uiw3rqPvNLBZFUhM4ZEatC83kB3teuBTgBr5PUQlyeVwnmMK0ghu4WBblDUT75yJi8tYQPIJUb8sqrHZgtz15kA= Received: by 10.90.100.2 with SMTP id x2mr1955400agb.1172849292838; Fri, 02 Mar 2007 07:28:12 -0800 (PST) Received: by 10.90.116.10 with HTTP; Fri, 2 Mar 2007 07:28:12 -0800 (PST) Message-ID: Date: Fri, 2 Mar 2007 10:28:12 -0500 From: "Alex Deucher" To: "Jeff Frost" Subject: Re: strange performance regression between 7.4 and 8.1 Cc: "Joshua D. Drake" , pgsql-performance@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <45E73C90.2080206@commandprompt.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200703/56 X-Sequence-Number: 23461 On 3/1/07, Jeff Frost wrote: > On Thu, 1 Mar 2007, Alex Deucher wrote: > > > here are some examples. Analyze is still running on the new db, I'll > > post results when that is done. Mostly what our apps do is prepared > > row selects from different tables: > > select c1,c2,c3,c4,c5 from t1 where c1='XXX'; > > > > old server: > > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261'; > > QUERY PLAN > > --------------------------------------------------------------------------------------------------------------------------- > > Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42 > > width=26) (actual time=5.722..5.809 rows=2 loops=1) > > Index Cond: ((c2)::text = '6258261'::text) > > Total runtime: 5.912 ms > > (3 rows) > > > > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261'; > > QUERY PLAN > > ---------------------------------------------------------------------------------------------------------------------------- > > Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72 > > width=26) (actual time=12.423..12.475 rows=12 loops=1) > > Index Cond: ((c1)::text = '6258261'::text) > > Total runtime: 12.538 ms > > (3 rows) > > > > > > new server: > > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261'; > > QUERY PLAN > > ---------------------------------------------------------------------------------------------------------------------------- > > Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11 > > width=26) (actual time=33.461..51.377 rows=2 loops=1) > > Index Cond: ((c2)::text = '6258261'::text) > > Total runtime: 51.419 ms > > (3 rows) > > > > db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261'; > > QUERY PLAN > > -------------------------------------------------------------------------------------------------------------------------------- > > Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907 > > width=26) (actual time=45.733..46.271 rows=12 loops=1) > > Index Cond: ((c1)::text = '6258261'::text) > > Total runtime: 46.325 ms > > (3 rows) > > Notice the huge disparity here betwen the expected number of rows (2907) and > the actual rows? That's indicative of needing to run analyze. The time is > only about 4x the 7.4 runtime and that's with the analyze running merrily > along in the background. It's probably not as bad off as you think. At least > this query isn't 10x. :-) > > Run these again for us after analyze is complete. well, while the DB isn't 10x, the application using the DB shoes a 10x decrease in performance. Pages that used to take 5 seconds to load take 50 secs (I supposed the problem is compounded as there are several queries per page.). Anyway, new numbers after the analyze. Unfortunately, they are improved, but still not great: old server: db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------- Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42 width=26) (actual time=0.204..0.284 rows=2 loops=1) Index Cond: ((c2)::text = '6258261'::text) Total runtime: 0.421 ms (3 rows) db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72 width=26) (actual time=0.299..0.354 rows=12 loops=1) Index Cond: ((c1)::text = '6258261'::text) Total runtime: 0.451 ms (3 rows) new server: db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6258261'; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11 width=26) (actual time=0.126..0.134 rows=2 loops=1) Index Cond: ((c2)::text = '6258261'::text) Total runtime: 0.197 ms (3 rows) db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6258261'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907 width=26) (actual time=5.820..5.848 rows=12 loops=1) Index Cond: ((c1)::text = '6258261'::text) Total runtime: 5.899 ms (3 rows) Here's another example: old server: db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6000001'; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Index Scan using t1_c2_index on t1 (cost=0.00..166.89 rows=42 width=26) (actual time=4.031..55.349 rows=8 loops=1) Index Cond: ((c2)::text = '6000001'::text) Total runtime: 55.459 ms (3 rows) db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6000001'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Index Scan using t1_c1_key on t1 (cost=0.00..286.08 rows=72 width=26) (actual time=0.183..0.203 rows=4 loops=1) Index Cond: ((c1)::text = '6000001'::text) Total runtime: 0.289 ms (3 rows) new server: db=# EXPLAIN ANALYZE select c1,c2 from t1 where c2='6000001'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------ Index Scan using t1_c2_index on t1 (cost=0.00..37.63 rows=11 width=26) (actual time=115.412..202.151 rows=8 loops=1) Index Cond: ((c2)::text = '6000001'::text) Total runtime: 202.234 ms (3 rows) db=# EXPLAIN ANALYZE select c1,c2 from t1 where c1='6000001'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Index Scan using t1_c1_index on t1 (cost=0.00..630.45 rows=2907 width=26) (actual time=99.811..99.820 rows=4 loops=1) Index Cond: ((c1)::text = '6000001'::text) Total runtime: 99.861 ms (3 rows) I haven't gotten a chance to restart postgres this the config changes you suggested yet. The rows have improved for some but not all and the times are still slow. Any ideas? Alex