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