Re: FW: Index usage - Mailing list 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@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