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
