Well you just selected a whole lot more rows... What's the total number of rows in the table?
In general, what I remember from reading on the list, is that when there's no upper bound on a query like this, the
planneris more likely to choose a seq. scan than an index scan.
Try to give your query an upper bound like:
select date from chatlogs where date>='11/23/04' and date < '12/31/99';
select date from chatlogs where date>='10/23/04' and date < '12/31/99';
This should make it easier for the planner to give a proper estimate of the number of rows returned. If it doesn't help
yet,please post 'explain analyze' output rather than 'explain' output, for it allows much better investigation into why
theplanner chooses what it chooses.
cheers,
--Tim
-----Original Message-----
From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org]On Behalf Of BBI Edwin
Punzalan
Sent: Wednesday, November 24, 2004 7:52 AM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] FW: Index usage
Hi everyone,
Can anyone please explain postgres' behavior on our index.
I did the following query tests on our database:
====================
db=# create index chatlogs_date_idx on chatlogs (date);
CREATE
db=# explain select date from chatlogs where date>='11/23/04';
NOTICE: QUERY PLAN:
Index Scan using chatlogs_date_idx on chatlogs (cost=0.00..144.11 rows=36
width=4)
EXPLAIN
db=# explain select date from chatlogs where date>='10/23/04';
NOTICE: QUERY PLAN:
Seq Scan on chatlogs (cost=0.00..23938.06 rows=253442 width=4)
EXPLAIN====================
Date's datatype is date. Its just odd that I just change the actual date of
search and the index is not being used anymore.
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
