X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A71ED47580B for ; Tue, 25 Feb 2003 07:58:44 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 04856-01 for ; Tue, 25 Feb 2003 07:58:33 -0500 (EST) Received: from mail.libertyrms.com (unknown [209.167.124.227]) by postgresql.org (Postfix) with ESMTP id 62977475E83 for ; Tue, 25 Feb 2003 07:44:47 -0500 (EST) Received: from andrew by mail.libertyrms.com with local (Exim 3.22 #3 (Debian)) id 18neS2-0004Jx-00 for ; Tue, 25 Feb 2003 07:44:50 -0500 Date: Tue, 25 Feb 2003 07:44:50 -0500 From: Andrew Sullivan To: pgsql-performance@postgresql.org Subject: Re: Query not using the index Message-ID: <20030225074450.A16284@mail.libertyrms.com> Mail-Followup-To: Andrew Sullivan , pgsql-performance@postgresql.org References: <200302251903.40364.mark@transportservices.com.au> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: <200302251903.40364.mark@transportservices.com.au>; from mark@transportservices.com.au on Tue, Feb 25, 2003 at 07:03:40PM +1100 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/195 X-Sequence-Number: 1251 On Tue, Feb 25, 2003 at 07:03:40PM +1100, Mark Halliwell wrote: > The majority of records (about 6.8 million) have computer = 8 with sequence > starting at 2200000 and incrementing by 1. > There are about 497000 records with computer = 3 with the sequence starting at > 1 and also incrementing by 1. > There are only a few records with other computer numbers. > select * from replicate where computer = 3 and sequence >= 490000; > > I have created several different indexes (always doing a vacuum analyse > afterwards etc), but the explain always reports a sequential scan. If I Try setting the statistics on computer to a much wider value -- say ALTER TABLE computer ALTER COLUMN computer SET STATISTICS 1000 and see if it helps. You can poke around in the pg_stats view to see why this might help, and perhaps to get a more realistic idea of what you need to set the statistics to. The problem is likely the overwhelming commonality of computer=8. A -- ---- Andrew Sullivan 204-4141 Yonge Street Liberty RMS Toronto, Ontario Canada M2P 2A8 +1 416 646 3304 x110