Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2013

    Unanswered: Evaluating the performance of count in postgresql 9.2.

    As an initial attempt to try hands-on with postgresql, I've learned on user created functions and some other internals of postgresql.

    Few materials on postgresql indicate that the aggregate functions are slow due to some internal reasons (index covering, null etc).

    I'm looking forward to implement my own count mechanism (I've gone through some bit twiddling as well) via user defined functions (in C) and in cases I may need to create user defined data types as well.

    Are there any possible scenarios where user defined count would perform better than the underlying implementation? Is it worth a try?

    And are there any better ways to evaluate, compare both? (other than EXPLAIN ANALYZIS)

    Please guide.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    There are many things that you need to consider. First of all different hardware architectures and operating systems will provide (sometimes radically) different results for user defined functions and aggregate functions will be more affected by these things than stochastic scalar functions. What works better on a specific platform (hardware and OS) may or may not perform better an another platform and probably will not perform better on all platforms.

    EXPLAIN ANALYSIS will only show you what PostgreSQL thinks it will do. It isn't really useful for performance monitoring or measurements. You need to build a suite of load tests (both direct and indirect) to judge performance. It would be a much more useful suite if you make it supportable on most or all platforms.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2003
    Provided Answers: 23
    Quote Originally Posted by Pat Phelan View Post
    EXPLAIN ANALYSIS will only show you what PostgreSQL thinks it will do.
    EXPLAIN ANALYZE will show what Postgres actually did because the query (the statement) is actually executed. EXPLAIN (without ANALYZE) will show what Postgres thinks it will do.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts