X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 02ADFB439D5 for ; Fri, 4 Jul 2003 01:57:59 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 86026-07 for ; Thu, 3 Jul 2003 22:57:48 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 37FFFB435E9 for ; Thu, 3 Jul 2003 22:57:48 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h641vZQQ010561; Thu, 3 Jul 2003 21:57:36 -0400 (EDT) To: Sean Chittenden Cc: "scott.marlowe" , pgsql-performance@postgresql.org Subject: Re: Moving postgresql.conf tunables into 2003... In-reply-to: <20030703232351.GS72567@perrin.int.nxad.com> References: <20030703190502.GR72567@perrin.int.nxad.com> <20030703232351.GS72567@perrin.int.nxad.com> Comments: In-reply-to Sean Chittenden message dated "Thu, 03 Jul 2003 16:23:51 -0700" Date: Thu, 03 Jul 2003 21:57:35 -0400 Message-ID: <10560.1057283855@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200307/41 X-Sequence-Number: 2334 Sean Chittenden writes: > Getting the planner to pick > using the index to filter out data inserted in the last 3 days over > doing a seq scan... well, I don't know how you could do that without > changing the random_page_cost. This sounds a *whole* lot like a correlation issue. If the data in question were scattered randomly in the table, it's likely that an indexscan would be a loser. The recently-inserted data is probably clustered near the end of the table (especially if they're doing VACUUM FULL after data purges; are they?). But the planner's correlation stats are much too crude to recognize that situation, if the rest of the table is not well-ordered. If their typical process involves a periodic data purge and then a VACUUM FULL, it might be worth experimenting with doing a CLUSTER on the timestamp index instead of the VACUUM FULL. The CLUSTER would reclaim space as effectively as VACUUM FULL + REINDEX, and it would leave the table with an unmistakable 1.0 correlation ... which should tilt the planner towards an indexscan without needing a physically impossible random_page_cost to do it. I think CLUSTER would probably be a little slower than VACUUM FULL but it's hard to be sure without trying. regards, tom lane