0

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 enter image description here Postgresql 9.1 enter image description here

4
  • How is 9.0 database maintenanced? Is autovacuum turned on? I'm guessing Your virtualised 9.1 copy is fresh restore. Another thing: short list of 9.1 changes (compared to 9.0) shows some big improvements on ordered queries with LIMIT and on OUTER JOIN. Those aren't touching Your issue but there might be something related to search about.
    – Marcin S.
    Sep 6 '16 at 12:32
  • There are other information in 9.1 release note under E.24.3.1.1. Performance and E.24.3.1.2. Optimizer, that might point to changes responsible for difference in Your execution times.
    – Marcin S.
    Sep 6 '16 at 12:39
  • 1
    Could you please paste the text version of your plans at explain.depesz.com?
    – MatheusOl
    Sep 6 '16 at 13:48
  • Text explains attached :) explain.depesz.com/s/8Pfg explain.depesz.com/s/Prg3 Sep 6 '16 at 14:00
0

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!

1
  • 2
    Note that both version are no longer maintained. You should really plan your upgrade as soon as possible. Sep 6 '16 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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