Wednesday, February 25, 2015

Stopping expensive queries before they start

Today we're releasing a code for a small PostgreSQL module called plan_filter that lets you stop queries from even starting if they meet certain criteria. Currently the module implements one such criterion: the estimated cost of the query.

After you have built and installed it, you add a couple of settings to the postgresql.conf file, like this:
shared_preload_libraries = 'plan_filter'
plan_filter.statement_cost_limit = 100000.0
Then if the planner estimates the cost as higher than the statement_cost_limit it will raise an error rather than allowing the query to run.

This module follows an idea from a discussion on the postgresql-hackers mailing list some time ago. It was developed by PostgreSQL Experts Inc for our client Twitch.TV, who have generously allowed us to make it publicly available.

6 comments:

  1. How can you define what's the cost limit more adapt for your needs? Also I cannot find in EXPLAIN docs how reliable is this information. Could you clarify please?
    Thank you.

    ReplyDelete
    Replies
    1. That's beyond the scope of this. Consult the postgres docs and mailing lists.

      Delete
  2. Can you specify the value per session instead of in the config?

    ReplyDelete
    Replies
    1. Yes, you can. You can also set it per user, or per database.

      Delete
  3. Hello Andrew,

    Can you please give me more details of this, how to Install and how to implement this ? I have tried this as suggested in https://github.com/pgexperts/pg_plan_filter. But still I'm not able to implement this. Please help me

    ReplyDelete
  4. Without details it's impossible to say what you did wrong. Were you able to build and install it? If not, what went wrong? If you were able to, then what is not working?

    ReplyDelete