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

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" <edwin@bluebamboo.ph>
To: "'Iain'" <iain@mst.co.jp>; "'gnari'" <gnari@simnet.is>;
<pgsql-performance@postgresql.org>
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:iain@mst.co.jp]
> Sent: Wednesday, December 01, 2004 12:00 PM
> To: BBI Edwin Punzalan; 'gnari'; pgsql-performance@postgresql.org
> 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" <edwin@bluebamboo.ph>
> To: "'gnari'" <gnari@simnet.is>; <pgsql-performance@postgresql.org>
> 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:gnari@simnet.is]
>> Sent: Wednesday, December 01, 2004 10:08 AM
>> To: BBI Edwin Punzalan; pgsql-performance@postgresql.org
>> Subject: Re: [PERFORM] FW: Index usage
>>
>>
>> From: "BBI Edwin Punzalan" <edwin@bluebamboo.ph>
>>
>>
>>>
>>> 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 majordomo@postgresql.org