Re: FW: Index usage

From: Iain
Subject: Re: FW: Index usage
Date: ,
Msg-id: 00e001c4d75c$defe7e40$7201a8c0@mst1x5r347kymb
(view: Whole thread, Raw)
In response to: Re: FW: Index usage  ("BBI Edwin Punzalan")
List: pgsql-performance

Tree view

FW: FW: Index usage  ("BBI Edwin Punzalan", )
 Re: FW: Index usage  ("gnari", )
  Re: FW: Index usage  ("BBI Edwin Punzalan", )
   Re: FW: Index usage  ("gnari", )
    Re: FW: Index usage  ("BBI Edwin Punzalan", )
     Re: FW: Index usage  ("gnari", )
      Re: FW: Index usage  ("BBI Edwin Punzalan", )
       Re: FW: Index usage  ("Iain", )
        Re: FW: Index usage  ("BBI Edwin Punzalan", )
         Re: FW: Index usage  ("Iain", )
       Re: FW: Index usage  ("gnari", )
 Re: FW: FW: Index usage  (Richard Huxton, )
  Re: FW: FW: Index usage  ("BBI Edwin Punzalan", )
   Re: FW: FW: Index usage  (Richard Huxton, )

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" <>
To: "'Iain'" <>; "'gnari'" <>;
<>
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:]
> Sent: Wednesday, December 01, 2004 12:00 PM
> To: BBI Edwin Punzalan; 'gnari'; 
> 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" <>
> To: "'gnari'" <>; <>
> 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:]
>> Sent: Wednesday, December 01, 2004 10:08 AM
>> To: BBI Edwin Punzalan; 
>> Subject: Re: [PERFORM] FW: Index usage
>>
>>
>> From: "BBI Edwin Punzalan" <>
>>
>>
>>>
>>> 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 



pgsql-performance by date:

From: Andrew McMillan
Date:
Subject: Re: Using "LIMIT" is much faster even though, searching
From: Rodrigo Carvalhaes
Date:
Subject: pg_restore taking 4 hours!