Re: [PERFORM] FW: Index usage

[Iain]

Sorry, i can't check this easily as I don't have any date fields in my data 
(they all held has character strings - do as i say, not as i do) but maybe 
you should cast or convert the string representation of the date to a date 
in the where clause. Postgres might be doing some implicit conversion but if 
it is, I'd expect it to use a YYYY-MM-DD format which is what I see here.

Something like ... WHERE date>= to_date('11/03/04','DD/MM/YY')
regards
Iain
----- Original Message ----- 
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]>
To: "'Iain'" <[EMAIL PROTECTED]>; "'gnari'" <[EMAIL PROTECTED]>; 
<[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 1:05 PM
Subject: RE: [PERFORM] FW: Index usage


Hi. Thanks for your reply.  The date column data type is date already. :D
-----Original Message-----
From: Iain [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 12:00 PM
To: BBI Edwin Punzalan; 'gnari'; [EMAIL PROTECTED]
Subject: Re: [PERFORM] FW: Index usage
If it's any help, i just ran this test on 7.4.6, my table has about 
7000000
rows and the index is an integer.

The item id ranges from 1 to 20000.
As you can see from the following plans, the optimizer changed it's plan
depending on the value of the item id condition, and will use an index 
when
it determines that the number of values that will be returned is a low % 
of
the total table size.

The item_id is an integer, but It looked like you are using a character
field to store date information. Also, the dates you entered in your test
case seem to be in the format DD/MM/YY which won't be amenable to useful
comparative searching (I didn't read any of the earlier posts so if that
isn't the case, just ignore this). If this is the case, try storing the 
data

in a date column and see what happens then.
regards
Iain
test=# explain analyse select * from bigtable where item_id <= 1000;
QUERY
PLAN
----------------------------------------------------------------------------
---------------------------------------
--------------------------------------------
Index Scan using d_bigtable_idx2 on bigtable  (cost=0.00..118753.57
rows=59553 width=80) (actual
time=0.069..704.401 rows=58102 loops=1)
  Index Cond: ((item_id)::integer <= 1000)
Total runtime: 740.786 ms
(3 rows)
test=# explain analyse select * from bigtable where item_id <= 100000000;
                                                           QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------
---------------
Seq Scan on d_hi_mise_item_uri  (cost=0.00..194285.15 rows=7140589
width=80) (actual time=0.027..18599.032 rows=71
14844 loops=1)
  Filter: ((item_id)::integer <= 100000000)
Total runtime: 23024.986 ms
----- Original Message ----- 
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]>
To: "'gnari'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Wednesday, December 01, 2004 11:33 AM
Subject: Re: [PERFORM] FW: Index usage


Thanks but whatever it does, it didn't work. :D
Do you think upgrading will fix this problem?
=========================
db=# alter table chatlogs alter column date set statistics 300; ALTER
db=# analyze chatlogs;
ANALYZE
db=# explain analyze select * from chatlogs where date >= '12/1/04';
NOTICE:  QUERY PLAN:
Index Scan using chatlogs_type_idx on chatlogs  (cost=0.00..6053.61
rows=3357 width=212) (actual time=22.14..138.53 rows=1312
loops=1)
Total runtime: 139.42 msec
EXPLAIN
morphTv=# explain analyze select * from chatlogs where date >=
'11/03/04';
NOTICE:  QUERY PLAN:
Seq Scan on chatlogs  (cost=0.00..27252.86 rows=271882 width=212)
(actual time=12.24..13419.36 rows=257137 loops=1) Total runtime:
13573.70 msec
EXPLAIN
=========================

-----Original Message-----
From: gnari [mailto:[EMAIL PROTECTED]
Sent: Wednesday, December 01, 2004 10:08 AM
To: BBI Edwin Punzalan; [EMAIL PROTECTED]
Subject: Re: [PERFORM] FW: Index usage
From: "BBI Edwin Punzalan" <[EMAIL PROTECTED]>

Hi, what do you mean by increasing the statistics on the date column?
alter table chatlogs alter column date set statistics 300; analyze
chatlogs;
> > Our version is 7.2.1
>
> upgrade time ?
We never had any upgrade on it.
7.2 is a bit dated now that 8.0 is in beta
if you want to stay with 7.2, you should at least upgrade
to the latest point release (7.2.6 ?), as several serious bugs have
been fixed
gnari
---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] 

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]