Why no performance boost although I added an index?

Hi *,

I have an accounting table on postgres 7.2.4, and my favourite select
gets no performance boost if I add an index on the date column. But it
should be faster.

Without index:

|db1=# explain select date,
|db1-#        to_char(sum(in_local),'9 999 999 999')                      as in,
|db1-#        to_char(sum(out_local),'9 999 999 999')                     as out,
|db1-#        to_char(sum(in_forward),'9 999 999 999')                    as in_f,
|db1-#        to_char(sum(out_forward),'9 999 999 999')                   as out_f,
|db1-#        to_char(sum(out_local + out_forward + in_local + in_forward),'9999 999 999 99 9') as total
|db1-#        from netacct
|db1-#        where date > date 'today' - interval '10 days'
|db1-#          and interface = 'ppp0'
|db1-#        group by date;
|NOTICE:  QUERY PLAN:
|
|Aggregate  (cost=214.29..236.19 rows=146 width=20)
| -> Group  (cost=214.29..217.94 rows=1460 width=20)
|   -> Sort  (cost=214.29..214.29 rows=1460 width=20)
|     ->  Seq Scan on netacct  (cost=0.00..137.55 rows=1460 width=20)
|
|Aggregate  (cost=215.13..237.13 rows=147 width=20)
|           (actual time=3152.03..3161.54 rows=11 loops=1)
|->  Group  (cost=215.13..218.80 rows=1467 width=20)
|           (actual time=3150.96..3154.93 rows=265 loops=1)
|  ->  Sort  (cost=215.13..215.13 rows=1467 width=20)
|            (actual time=3150.93..3151.46 rows=265 loops=1)
|    ->  Seq Scan on netacct  (cost=0.00..138.00 rows=1467 width=20)
|                             (actual time=2950.10..3147.15 rows=265 loops=1)
|Total runtime: 3162.27 msec


And now after a "create index netacct_ix1 on netacct(date)" and vacuum analyze:

|Aggregate  (cost=0.00..176.40 rows=147 width=20)
| ->  Group  (cost=0.00..158.07 rows=1467 width=20)
|   ->  Index Scan using netacct_ix1 on netacct  (cost=0.00..154.40 rows=1467 width=20

|Aggregate  (cost=0.00..176.40 rows=147 width=20)
|           (actual time=3128.57..3337.59 rows=11 loops=1)
|->  Group  (cost=0.00..158.07 rows=1467 width=20)
|           (actual time=3108.24..3327.61 rows=265 loops=1)
|  ->  Index Scan using netacct_ix1 on netacct (cost=0.00..154.40 rows=1467 width=20)
|                                              (actual time=3108.21..3322.22 rows=265 loops=1)
|Total runtime: 3338.37 msec

So the index is used, but the execution time is greater than without
index. Why that? Is the overhead using an index the biggest factor
because there are only a few thousand rows in the table?

--
PGP/GPG Key-ID:
http://blackhole.pca.dfn.de:11371/pks/lookup?op=get&search=0xB5A1AFE1