Indexes?

Hello all,

I have a question related to the use of indexes. One of my tables is part of
a census, with over 2.5 million records in it. Clearly, just about any query
takes a rather long time to complete. I was hoping to reduce the amount of
time by using indexes. However, no matter what I do, PostgreSQL never seems
to use them. It would seem to make sense that if I calculate something
grouped by a set of variables that have an index created for them, that the
index should be used rather than a sequential search, but it doesn't. I have
a table with an index such as the one created below:

CREATE INDEX tbl_censo_poblacion_1993_manzana_idx ON tbl_censo_poblacion_1993
( dubicacion, zona, manzana );

Then I try a query such as:

select count(*) as POBLACION, (dubicacion || zona || manzana) as COD_MANZANA
from tbl_censo_poblacion_1993 group by dubicacion, zona, manzana;

The results from explain indicate that a sequential scan is used (as far as I
can tell). I tried adding where statement:

select count(*) as POBLACION, (dubicacion || zona || manzana) as COD_MANZANA
from tbl_censo_poblacion_1993 where dubicacion <> '' and zona <> '' and
manzana <> '' group by dubicacion, zona, manzana;

The EXPLAIN analysis results appear as follows:

Aggregate (cost=847587.90..879024.28 rows=251491 width=27) (actual
time=272782.00..279458.00 rows=21459 loops=1)
-> Group (cost=847587.90..872737.01 rows=2514911 width=27) (actual
time=272782.00..278546.00 rows=2553015 loops=1)
-> Sort (cost=847587.90..853875.18 rows=2514911 width=27) (actual
time=272782.00..274533.00 rows=2553015 loop
s=1)
Sort Key: dubicacion, zona, manzana
-> Seq Scan on tbl_censo_poblacion_1993 (cost=0.00..328346.76
rows=2514911 width=27) (actual time=0.00. .189570.00 rows=2553015 loops=1)
Filter: ((dubicacion <> ''::character varying) AND (zona
<> ''::character varying) AND (manzana <> ''::character varying)) Total
runtime: 279494.00 msec (7 rows)

So...can anyone suggest to me what I could do? I'm using PostgreSQL 7.3.4-2
in Cygwin on a WinXP platform.

As an aside, is there any way to increase the amount of memory allocated to
the postmaster.exe process? It seems to me that if I could add more than 4MB
that it has by default, then maybe that could increase the performance.

Any help is appreciated.

Kind regards,
Mike

----------------------------------------
This mail sent through www.mywaterloo.ca