Run query faster, what index to use I have a table with 85M rows :

CREATE TABLE emissions_totales ( 
"schema_source" varchar(150), 
"snap" varchar(20), 
"gid" varchar(255), 
"polluant" varchar(50), 
"emi" float8, 
"numreg" varchar(3), 
"unites" varchar(60), 
"annee_ref" varchar(4), 
"type_source_n1" varchar(255), 
"type_source_n2" varchar(255), 
"type_source_n3" varchar(255), 
"napfue" varchar(15), 
"naf_rev2" varchar(10), 
"entite" varchar(255), 
"id" int4)


but a query like this one:

SELECT
    numreg,
    annee_ref,
    type_source_n1,
    polluant,
    unites,
    sum(emi) as emi
FROM emissions_synthese.emissions_totales
WHERE schema_source='residentiel'
GROUP BY
    numreg,
    annee_ref,
    type_source_n1,
    polluant,
    unites;


takes more than 3 hours to perform. What is the best way to make indexes? Do I have to make unique index on schema_source, numreg, annee_ref, type_source_n1, polluant and unites or a multicolumn index?

I have modified the postgresql.conf file by reading various sites, do I have to change something?

shared_buffers = 956MB
work_mem = 5MB
maintenance_work_mem = 128MB
checkpoint_segments = 64
checkpoint_timeout = 5min
checkpoint_completion_target = 0.9
effective_cache_size = 2304MB


(postgresql 8.4 on Debian Squeeze, 3.0 Go RAM, 2 Intel Xeon E5345 CPU)
I think the main problem is in your table design (many-many redundant values which are prone to typos and other problems).  If you were referencing these values via foreign keys, the table rows would be much shorter - therefore the aggregation could be done on a much smaller dataset and the actual values be added via joins.  (The optimizer takes care of doing this well, you don't need additional magic.  The design change is your task, though :) - see also ypercube's comment above.)

Aside this, in your configuration the work_mem value seems to be very low.  If this is true or not depends on how many rows you get from this query - but as grouping and aggregating involves sorting and work_mem is the place where the data is being sorted, there is a chance that simply increasing this to a (much) bigger value would improve the performance a lot.

Furthermore, an index like

CREATE INDEX idx_emissions_totales_residentiel
ON emissions_totales (schema_source, numreg, annee_ref, type_source_n1, unites)
WHERE schema_source = 'residentiel';


will also help this particular query.  As edze suggested, going to PostgreSQL 9.2 and including 'emi' in the index (as the last column) could also help.