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 planner is 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 the planner chooses what it chooses.

cheers,

--Tim


-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of BBI Edwin Punzalan
Sent: Wednesday, November 24, 2004 7:52 AM
To: [EMAIL PROTECTED]
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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

Reply via email to