index scan on =, but not < ?

From: Rick Schumeyer
Subject: index scan on =, but not < ?
Date: ,
Msg-id: 001f01c5240d$a920c8c0$0200a8c0@dell8200
(view: Whole thread, Raw)
Responses: Re: index scan on =, but not < ?  (Thomas F.O'Connell)
Re: index scan on =, but not < ?  (John Arbash Meinel)
Re: index scan on =, but not < ?  (Dennis Bjorklund)
Re: index scan on =, but not < ?  (Bruno Wolff III)
List: pgsql-performance

Tree view

index scan on =, but not < ?  ("Rick Schumeyer", )
 Re: index scan on =, but not < ?  (Thomas F.O'Connell, )
  Re: index scan on =, but not < ?  ("Rick Schumeyer", )
 Re: index scan on =, but not < ?  (John Arbash Meinel, )
 Re: index scan on =, but not < ?  (Dennis Bjorklund, )
 Re: index scan on =, but not < ?  (Bruno Wolff III, )
  Re: index scan on =, but not < ?  ("Jim C. Nasby", )
   Re: index scan on =, but not < ?  (Bruno Wolff III, )
    Re: index scan on =, but not < ?  ("Jim C. Nasby", )
     Re: index scan on =, but not < ?  (David Brown, )
      Re: index scan on =, but not < ?  (Manfred Koizar, )
    Re: index scan on =, but not < ?  (David Brown, )

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)


pgsql-performance by date:

From: Josh Berkus
Date:
Subject: Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?
From: Josh Berkus
Date:
Subject: Re: Why would writes to pgsql_tmp bottleneck at 1mb/s?