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 8AF6856401 for ; Tue, 8 Mar 2005 18:36:06 +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 55985-08 for ; Tue, 8 Mar 2005 18:35:57 +0000 (GMT) Received: from rwcrmhc11.comcast.net (rwcrmhc14.comcast.net [216.148.227.89]) by svr1.postgresql.org (Postfix) with ESMTP id A8B865514D for ; Tue, 8 Mar 2005 18:35:54 +0000 (GMT) Received: from dell8200 (pcp05158174pcs.rte20201.de.comcast.net[69.139.132.79]) by comcast.net (rwcrmhc14) with SMTP id <20050308183553014001k0mqe>; Tue, 8 Mar 2005 18:35:53 +0000 From: "Rick Schumeyer" To: Subject: index scan on =, but not < ? Date: Tue, 8 Mar 2005 13:35:53 -0500 Message-ID: <001f01c5240d$a920c8c0$0200a8c0@dell8200> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0020_01C523E3.C04AC0C0" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook, Build 10.0.6626 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180 Importance: Normal X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=AWL, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200503/132 X-Sequence-Number: 10837 This is a multi-part message in MIME format. ------=_NextPart_000_0020_01C523E3.C04AC0C0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have two index questions. The first is about an issue that has been recently discussed, and I just wanted to be sure of my understanding. Functions like count(), max(), etc. will use sequential scans instead of index scans because the index doesn't know which rows are actually visible.is this correct? Second: 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: =# explain select * from data where x = 0; QUERY PLAN ------------------------------------------------------------------------- Index Scan using data_x_ix on data (cost=0.00..78.25 rows=19 width=34) Index Cond: (x = 0::double precision) (2 rows) But this command, in which the only difference if > instead of =, is a sequential scan. =# explain select * from data where x > 0; QUERY PLAN ------------------------------------------------------------------ Seq Scan on data (cost=0.00..1722605.20 rows=62350411 width=34) 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.if it matters) ------=_NextPart_000_0020_01C523E3.C04AC0C0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have two index questions.  The first is about = an issue that has been recently discussed,

and I just wanted to be sure of my = understanding.  Functions like count(), max(), etc. will

use sequential scans instead of index scans because = the index doesn’t know which rows

are actually visible…is this = correct?

 

Second:

 

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;

         =             &= nbsp;         QUERY PLAN

------------------------------------------------------= -------------------

 Index Scan using data_x_ix on data  = (cost=3D0.00..78.25 rows=3D19 width=3D34)

   Index Cond: (x =3D 0::double = precision)

(2 rows)

 

 

But this command, in which the only difference if = > instead of =3D, is a sequential scan.

 

=3D# explain select * from data where x > = 0;

         =             &= nbsp;      QUERY PLAN

------------------------------------------------------= ------------

 Seq Scan on data  (cost=3D0.00..1722605.20 rows=3D62350411 width=3D34)

   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…if it matters)

------=_NextPart_000_0020_01C523E3.C04AC0C0--