SQL Join too slow on postgresql 9.0 vs postgresql 9.1

Question

There is a big difference in execution time, altough the ammount of table data are pretty much the same in both databases. On production DB - Postgresql 9.0 DB hosted by webprovider it takes 29seconds, versus of Postgresql 9.1 installed on virtual machine.

Also seems when I remove the second condition in ON section it runs fast but when there are both ON conditions it runs super slow.

a) I've tried to change type from varchar(255) to text and vice versa for type reference_number_1 so it would match document_number type varchar(255).

b) Tried to REINDEX command for both tables

SELECT dro.document_number, dro.document_year, dro.document_date, dro.process_id   
FROM data_recent_orders dro  
LEFT JOIN data_tracking_system dts 
ON
dts.reference_number_1_metaapp = dro.document_number AND 
dts.reference_number_1_year_metaapp = dro.document_year  
WHERE
dro.document_type = 'Sofortrechnung'    
LIMIT 10000;

Postgresql 9.0 Postgresql 9.1

Answer 1

Seems the optimalization came with adding combined index on both two fields.

This was unnecesarry on postgres 9.1 machine but on postgres 9.0 adding combined index boosted the performance rocket like.

Thank you very much, for the answers!