Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
1  
The index you suggest seems good, one improvement would be to include the emi column as well. Another possible change would be to convert the wide varchar columns to integer columns (and some reference tables) so the compound index is as narrow as possible. –  ypercube May 30 '13 at 10:36
2  
Can you upgrade to postgres 9.2? This version can use index-only scans wiki.postgresql.org/wiki/Index-only_scans –  edze May 30 '13 at 16:06
    
Did you get somewhere with this problem? –  dezso Jun 3 '13 at 20:50
add comment

2 Answers

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 do 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 will 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 last column) could also help.

share|improve this answer
add comment

You might need to bump maintenance_work_mem up (300MB? 500MB?) before you create the indexes.

Try an index on schema_source, or if you know you only need rows that match the value 'residentiel' you can create a partial index like

create index emissions_totales_ss_residentiel_pidx 
on emissions_totales ( schema_source ) 
where schema_source = 'residentiel';

Try also a multi-column index on your GROUP BY columns (numreg, annee_ref, type_source_n1, polluant, unites)

When index creation finished, run

ANALYZE emissions_totales;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.