Postgres efficiency

Question

Lets say that I have a table with some key, accounts, and hits.

Which is faster? I guess my question is if postgres's (or any sql for that matter) has an optimizer intelligent to see identical functions in a query

SELECT key, accounts, hits,1.0*hits/accounts as ratio FROM
(
    SELECT key, COUNT(DISTINCT accounts) as accounts, SUM(hits) as hits
    FROM table
    GROUP BY key
) a;

OR

SELECT key, COUNT(DISTINCT accounts) as accounts, SUM(hits) as hits, 1.0*SUM(hits)/COUNT(DISTINCT accounts) as ratio
FROM table
GROUP BY key;

I'd love to hear anything you have to say or resources you can provide on understanding this sort of thing. Thanks!

Without knowing the tables, indexes, and data, it's going to be really hard to say. Have you tried EXPLAINing it? postgresql.org/docs/8.1/static/sql-explain.html — Ken
– Ken, Commented May 12, 2011 at 15:48
Why is an explain necessary? The question is if the optimizer is intelligent enough to realize that the COUNT(DISTINCT accounts) is identical in two places, as well as the SUM(hits), and if it will calculate each one once or twice. — A Question Asker
– A Question Asker, Commented May 12, 2011 at 15:54
If I do use Explain, however, the subquery version is marginally cheaper (819804 vs 819807) — A Question Asker
– A Question Asker, Commented May 12, 2011 at 15:57
explain tells you how the query planner will run the query. That will tell you if it's doing something twice or only once — Scott Marlowe
– Scott Marlowe, Commented May 12, 2011 at 17:01
819804 is not "marginally cheaper" than 819807. That's effectively identical. — Andy Lester
– Andy Lester, Commented Jul 29, 2013 at 22:09

Denis de Bernardy · Accepted Answer · 2011-05-12 16:11:46Z

3

Yes, it is. That is what function volatility is for.

http://www.postgresql.org/docs/current/static/sql-createfunction.html

See VOLATILE vs STABLE vs IMMUTABLE.

edited May 12, 2011 at 16:11

answered May 12, 2011 at 15:48

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Denis de Bernardy Over a year ago

@Leonbloy: immutable functions are called by the planner if possible; they and stable functions are then processed only once within the same statement. Calling select stable_foo(), stable_foo(), volatile_bar(), volatile_bar(); will result in a single call to stable_foo() but two calls to volatile_bar().

leonbloy Over a year ago

yeah, i know that, what i dont get is how that answers the question

Denis de Bernardy Over a year ago

His question is: "my question is if postgres's (...) has an optimizer intelligent to see identical functions in a query". Answer is yes, and it even spots identical functions with identical arguments. :-)

leonbloy Over a year ago

Ah, ok. I didn't read well the question... 'identical functions' should rather be 'identical function calls', perhaps.

Collectives™ on Stack Overflow

Postgres efficiency

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related