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 second: compare the number of rows in question. An index is extremely useful if 19 rows will be returned. But when 62350411 rows will be returned, you're talking about a substantial fraction of the table. A sequential scan will probably correctly be judged to be faster by the 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. 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)
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
