X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id D447C475E2B for ; Tue, 25 Feb 2003 10:21:02 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 40732-02 for ; Tue, 25 Feb 2003 10:20:46 -0500 (EST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 007AA475AAC for ; Tue, 25 Feb 2003 10:13:23 -0500 (EST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.6/8.12.6) with ESMTP id h1PFDR5u010479; Tue, 25 Feb 2003 10:13:27 -0500 (EST) To: Mark Halliwell Cc: pgsql-performance@postgresql.org Subject: Re: Query not using the index In-reply-to: <200302251903.40364.mark@transportservices.com.au> References: <200302251903.40364.mark@transportservices.com.au> Comments: In-reply-to Mark Halliwell message dated "Tue, 25 Feb 2003 19:03:40 +1100" Date: Tue, 25 Feb 2003 10:13:26 -0500 Message-ID: <10478.1046186006@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new X-Archive-Number: 200302/197 X-Sequence-Number: 1253 Mark Halliwell writes: > 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. You aren't going to find any non-kluge solution, because Postgres keeps no cross-column statistics and thus is quite unaware that there's any correlation between the computer and sequence fields. So in a query like > select * from replicate where computer = 3 and sequence >= 490000; the sequence constraint looks extremely unselective to the planner, and you get a seqscan, even though *in the domain of computer = 3* it's a reasonably selective constraint. > that if a specify an upper limit for sequence (a value which I cannot always > easily predict), it also uses the index. I would think that it'd be sufficient to say select * from replicate where computer = 3 and sequence >= 490000 and sequence < 2200000; If it's not, try increasing the statistics target for the sequence column so that ANALYZE gathers a finer-grain histogram for that column. regards, tom lane