up vote 0 down vote favorite

I noticed following query in my code and want to check if this can be optimized.

UPDATE table as T1 SET C1=? 
  FROM 
    (SELECT C2, C3, C4 
        FROM table 
        WHERE C1=? and current_timestamp >= C5 
        ORDER BY C5 limit ? FOR UPDATE
    ) AS T2 
WHERE T1.C2 = T2.C2 AND T1.C3 = T2.C3 AND T1.C4 = T2.C4
RETURNING *;

index on C2, C3

partitioned on C5

table :

C1, C2, C3 - varchar
C4, C5 - timestamp

share|improve this question
4  
Impossible to answer with just a very cryptic query. Use explain analyze to figure out where postgresql spend it's time. If you need help with the output of explain analyze insert it into your question. – Eelke Sep 27 '13 at 7:35
1  
There is no guarantee that the subquery returns exactly one (or zero) row(s) for every target row. Please fix your logic first before trying to optimise your performance. (order by .. limit 1 is a terrible way to restrict the subquery to one row, if that was your intention) – joop Sep 27 '13 at 8:45
up vote 0 down vote

Premature optimization is the root of all evil in databases. Design with sanity in mind and then show us when you get into a problem. Rather than answering this question I am going to explain why it cannot be answered.

SQL is a declarative language, where you provide something kind of like a mathematical formula to the database system and it figures out how to run it optimally. Well, technically the query is a mathematical formula but the math mirrors SQL and SQL approximates another field of mathematics known as relational algebra.

The actual optimization process depends highly on read and write patterns, as well as the limitations of knowledge of the planner. Until you have an actual bottleneck there is no way of assessing the relative performance of one query over another, unless you do certain things not present in your example (NOT EXISTS tends to be expensive and one tends to do better to write it as an outer join and filtering against cases of an inner join rather than as an antijoin. Presumably this will be addressed in the future). Even there, there are plenty of cases where an anti-join won't make a big difference and where gains in performance may not be worth worrying about.

So the point is you need to wait until you have an actual problem before optimizing the queries. Optimizing storage is very different, however.

share|improve this answer

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.