Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UXhEM-0003k0-Ko for pgsql-performance@arkaria.postgresql.org; Thu, 02 May 2013 00:18:38 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UXhEM-0007Uv-0g for pgsql-performance@arkaria.postgresql.org; Thu, 02 May 2013 00:18:38 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UXhEL-0007Up-2L for pgsql-performance@postgresql.org; Thu, 02 May 2013 00:18:37 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UXhEH-0008WS-VI for pgsql-performance@postgresql.org; Thu, 02 May 2013 00:18:36 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.5/8.14.5) with ESMTP id r420IWmh007769; Wed, 1 May 2013 20:18:32 -0400 (EDT) From: Tom Lane To: Craig James cc: "pgsql-performance@postgresql.org" Subject: Re: 100x slowdown for nearly identical tables In-reply-to: References: Comments: In-reply-to Craig James message dated "Wed, 01 May 2013 16:35:33 -0700" Date: Wed, 01 May 2013 20:18:32 -0400 Message-ID: <7768.1367453912@sss.pgh.pa.us> X-Pg-Spam-Score: -4.5 (----) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Craig James writes: > I have two tables that are nearly identical, yet the same query runs 100x > slower on the newer one. ... > db=> explain analyze select id, 1 from str_conntab > where (id >= 12009977 and id <= 12509976) order by id; > Index Scan using new_str_conntab_pkey_3217 on str_conntab > (cost=0.00..230431.33 rows=87827 width=4) > (actual time=65.771..51341.899 rows=48613 loops=1) > Index Cond: ((id >= 12009977) AND (id <= 12509976)) > Total runtime: 51350.556 ms > db=> explain analyze select id, 1 from old_str_conntab > where (id >= 12009977 and id <= 12509976) order by id; > Index Scan using str_conntab_pkey on old_str_conntab > (cost=0.00..82262.56 rows=78505 width=4) > (actual time=38.327..581.235 rows=48725 loops=1) > Index Cond: ((id >= 12009977) AND (id <= 12509976)) > Total runtime: 586.071 ms It looks like old_str_conntab is more or less clustered by "id", and str_conntab not so much. You could try EXPLAIN (ANALYZE, BUFFERS) (on newer PG versions) to verify how many distinct pages are getting touched during the indexscan. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance