X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 811C35641F for ; Tue, 8 Mar 2005 18:52:46 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60793-06 for ; Tue, 8 Mar 2005 18:52:44 +0000 (GMT) Received: from window.monsterlabs.com (window.monsterlabs.com [216.183.105.176]) by svr1.postgresql.org (Postfix) with SMTP id B788E56401 for ; Tue, 8 Mar 2005 18:52:42 +0000 (GMT) Received: (qmail 29133 invoked from network); 8 Mar 2005 18:52:43 -0000 Received: from host-209.149.56.238.nashville.net (HELO ?10.0.1.2?) (209.149.56.238) by 0 with SMTP; 8 Mar 2005 18:52:43 -0000 In-Reply-To: <001f01c5240d$a920c8c0$0200a8c0@dell8200> References: <001f01c5240d$a920c8c0$0200a8c0@dell8200> Mime-Version: 1.0 (Apple Message framework v619.2) Content-Type: text/plain; charset=WINDOWS-1252; delsp=yes; format=flowed Message-Id: <9b7a46c3c46a4fdf1beeef7eb5727493@sitening.com> Content-Transfer-Encoding: quoted-printable Cc: From: Thomas F.O'Connell Subject: Re: index scan on =, but not < ? Date: Tue, 8 Mar 2005 12:52:41 -0600 To: "Rick Schumeyer" X-Mailer: Apple Mail (2.619.2) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.004 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/133 X-Sequence-Number: 10838 Your hypothesis about index usage of count() and max() is correct. As for why you see index usage in your first example query and not your =20= second: compare the number of rows in question. An index is extremely =20= useful if 19 rows will be returned. But when 62350411 rows will be =20 returned, you're talking about a substantial fraction of the table. A =20= sequential scan will probably correctly be judged to be faster by the =20= planner. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Mar 8, 2005, at 12:35 PM, Rick Schumeyer wrote: > I have two index questions.=A0 The first is about an issue that has = been =20 > recently discussed, > and I just wanted to be sure of my understanding.=A0 Functions like =20= > count(), max(), etc. will > use sequential scans instead of index scans because the index doesn=92t = =20 > know which rows > are actually visible=85is this correct? > > =A0 > > Second: > > =A0 > > I created an index in a table with over 10 million rows. > The index is on field x, which is a double. > > The following command, as I expected, results in an index scan: > > =3D# explain select * from data where x =3D 0; > > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 QUERY PLAN > > = -----------------------------------------------------------------------=20= > -- > =A0Index Scan using data_x_ix on data=A0 (cost=3D0.00..78.25 rows=3D19 = =20 > width=3D34) > =A0=A0 Index Cond: (x =3D 0::double precision) > (2 rows) > =A0 > > But this command, in which the only difference if > instead of =3D, is = a =20 > sequential scan. > > > =3D# explain select * from data where x > 0; > > =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 QUERY PLAN > > ------------------------------------------------------------------ > > =A0Seq Scan on data=A0 (cost=3D0.00..1722605.20 rows=3D62350411 = width=3D34) > =A0=A0 Filter: (x > 0::double precision) > (2 rows) > > Why is this? > > (This is with pg 8.0.1 on a PC running FC3 with 1GB ram=85if it = matters)