Skip site navigation (1) Skip section navigation (2)

Re: FW: Index usage

From: "Iain" <iain(at)mst(dot)co(dot)jp>
To: "BBI Edwin Punzalan" <edwin(at)bluebamboo(dot)ph>,"'gnari'" <gnari(at)simnet(dot)is>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: FW: Index usage
Date: 2004-12-01 04:00:24
Message-ID: 00d101c4d75a$4bdc1700$7201a8c0@mst1x5r347kymb (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)bluebamboo(dot)ph>
To: "'gnari'" <gnari(at)simnet(dot)is>; <pgsql-performance(at)postgresql(dot)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(at)simnet(dot)is]
> Sent: Wednesday, December 01, 2004 10:08 AM
> To: BBI Edwin Punzalan; pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] FW: Index usage
>
>
> From: "BBI Edwin Punzalan" <edwin(at)bluebamboo(dot)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(at)postgresql(dot)org 


In response to

Responses

pgsql-performance by date

Next:From: BBI Edwin PunzalanDate: 2004-12-01 04:05:18
Subject: Re: FW: Index usage
Previous:From: BBI Edwin PunzalanDate: 2004-12-01 02:33:15
Subject: Re: FW: Index usage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group