1

Is there a way to speed up our plpgsql function that counts certain types of docs all in one query which is executed in a loop? ALL in one query?

validador := (select count(id_doc) from webdte.doc_tip_cifra
              where id_doc = id_documento and id_tipo_cifra = 901); 

validador2 := (select count(id_doc) from webdte.doc_tip_cifra
               where id_doc = id_documento and id_tipo_cifra = 902); 

validador3 := (select count(id_doc) from webdte.doc_tip_cifra
               where id_doc = id_documento and id_tipo_cifra = 905); 

validador4 := (select count(id_doc) from webdte.doc_tip_cifra
               where id_doc = id_documento and id_tipo_cifra = 907);
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
mugdiman
  • 101
  • 2
  • 11

1 Answers1

3

It should be faster to assign all four variables in one query (only one table or index scan):

SELECT INTO validador, validador2, validador3, validador4
            sum(CASE id_tipo_cifra WHEN 901 THEN 1 ELSE 0 END)
           ,sum(CASE id_tipo_cifra WHEN 902 THEN 1 ELSE 0 END)
           ,sum(CASE id_tipo_cifra WHEN 905 THEN 1 ELSE 0 END)
           ,sum(CASE id_tipo_cifra WHEN 907 THEN 1 ELSE 0 END)
FROM   webdte.doc_tip_cifra
WHERE  id_doc = id_documento;

Same result.

Normally you would have to check id_doc for NULL in addition, but since you have a WHERE condition with = on it, it cannot be NULL.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thank you for your answer, but unfortunately it is still to slow. It takes forever on 20000 documents. I put indexes on all id columns but still unusable. |If there is no way to further optimize it, I would completely go away from the idea to test all documents for 4 different ids and insert the null rows on an insert trigger. – mugdiman Jul 24 '12 at 01:44
  • Just tested on a table with 25k rows, the above query took under 100 ms, without any index. There is something off with your table or your installation. Run `VACUUM FULL ANALYZE webdte.doc_tip_cifra`, then retry the query. Any change? BTW, if `id_doc = id_documento` is true for sizable parts of the table (more than ~ 5 %, depending on row size and other factors), an **index is not going to help** with this query, because a table scan is faster. You should not just "put indexes on all id columns". Only create indexes you actually need - they carry a cost, too. – Erwin Brandstetter Jul 24 '12 at 02:26