FW: FW: Index usage - Mailing list pgsql-performance

Thanks, Tim.

I tried adding an upper limit and its still the same as follows:

==============
db=# explain analyze select date from chatlogs where date>='11/24/04';
NOTICE:  QUERY PLAN:

Index Scan using chatlogs_date_idx on chatlogs  (cost=0.00..145.72 rows=37
width=4) (actual time=0.18..239.69 rows=10737 loops=1)
Total runtime: 246.22 msec

EXPLAIN
db=# explain analyze select date from chatlogs where date>='11/23/04' and
date<'11/24/04';
NOTICE:  QUERY PLAN:

Seq Scan on chatlogs  (cost=0.00..24763.19 rows=9200 width=4) (actual
time=0.44..4447.01 rows=13029 loops=1)
Total runtime: 4455.56 msec

EXPLAIN
db=# explain analyze select date from chatlogs where date>='11/23/04' and
date<'11/25/04';
NOTICE:  QUERY PLAN:

Seq Scan on chatlogs  (cost=0.00..24763.19 rows=9200 width=4) (actual
time=0.45..4268.00 rows=23787 loops=1)
Total runtime: 4282.81 msec
==============

How come a query on the current date filter uses an index and the others
does not?  This makes indexing to speed up queries quite difficult.

-----Original Message-----
From: Leeuw van der, Tim [mailto:tim.leeuwvander@nl.unisys.com]
Sent: Wednesday, November 24, 2004 3:35 PM
To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
Subject: RE: [PERFORM] FW: Index usage


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@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