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: pgsql-performance-owner@xxxxxxxxxxxxxx [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx]On Behalf Of BBI Edwin Punzalan Sent: Wednesday, November 24, 2004 7:52 AM To: pgsql-performance@xxxxxxxxxxxxxx Subject: 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
