From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
To: <pgsql-performance@postgresql.org>
Subject: FW: FW: Index usage
Date: Wed, 24 Nov 2004 16:07:37 +0800
Message-ID: <001d01c4d1fc$ab997060$b400a8c0@bluebamboo.ph>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Importance: Normal


Thanks, Tim.

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

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

Index Scan using chatlogs_date_idx on chatlogs  (cost=3D0.00..145.72 =
rows=3D37
width=3D4) (actual time=3D0.18..239.69 rows=3D10737 loops=3D1)
Total runtime: 246.22 msec

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

Seq Scan on chatlogs  (cost=3D0.00..24763.19 rows=3D9200 width=3D4) =
(actual
time=3D0.44..4447.01 rows=3D13029 loops=3D1)
Total runtime: 4455.56 msec

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

Seq Scan on chatlogs  (cost=3D0.00..24763.19 rows=3D9200 width=3D4) =
(actual
time=3D0.45..4268.00 rows=3D23787 loops=3D1)
Total runtime: 4282.81 msec
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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]=20
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>=3D'11/23/04' and date < =
'12/31/99';

select date from chatlogs where date>=3D'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:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
db=3D# create index chatlogs_date_idx on chatlogs (date);
CREATE
db=3D# explain select date from chatlogs where date>=3D'11/23/04';
NOTICE:  QUERY PLAN:

Index Scan using chatlogs_date_idx on chatlogs  (cost=3D0.00..144.11 =
rows=3D36
width=3D4)

EXPLAIN
db=3D# explain select date from chatlogs where date>=3D'10/23/04';
NOTICE:  QUERY PLAN:

Seq Scan on chatlogs  (cost=3D0.00..23938.06 rows=3D253442 width=3D4)

EXPLAIN=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

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