Introduction to pg_stat_statements

hi everyone this is Darren from Postgres course comm and today what I want to share is what many people say is the most useful module provided in Postgres this modules provide as an extension it's called PG stat statements and so let's just get started taking a look at PG stat statements so what I'll show you is here on this machine I don't yet have it installed instead I'll need to install it so for many extensions including PG stat statements these extensions come along as a package call contribs approaches with Postgres so if you have a contributor with the method that you used to install your Postgres then you're already good to go but in case you don't this would be a sample command that you'd use for say a red hat or cent OS based system or I'll just say install the contribs these are additional contributed modules that go on top of the core Postgres product alright so that's installed and that will help me get access to a number of extensions including PG stat statements once I actually have it installed I'm going to switch over to my post-grad system user and I want to enable that extension this particular extension requires that I modify the PostgreSQL comm file and add PG stat statements to the shared pre lo library setting so I'll take that step by step and explain what it means the first thing that you'll need to do is know where your PostgreSQL comp file lives a couple ways you could do that is you could echo the PG data environment variable this is not a method that always works this requires that your installation method did set an environment variable called PG data and I can locate where that's set up so that's the data directory for my currently running cluster another way I could find out where my PostgreSQL comp file is located is simply by logging into my cluster and then I could type the command show data underscore directory and there I get the same output in any case once I've located it I can edit my PostgreSQL comp file so I'll edit that I'll use the environment variable to shortcut the path I'm looking for is this setting here shared pre-load libraries and I'll just change the terminal background really quickly to make that easier to see there's a setting shared preload libraries so I will uncomment that line I'll enter into that PG stat the shared preload library setting essentially allows the PG stat statements extension access to additional shared memory so because that's what that extension needs then I'm able to add the module this way and provide access to additional shared memory notice here this change will require restart I'm going to change my screen back so after I make that change then I'll restart my cluster clusters restarted so what's the next step well I want to specifically install this extension on a database named Oregon now that's one other thing you should know about PG stat statements is it's needs to be installed and enabled on a database by database method so the safes change I just made to add PG stat statements to share preload libraries in the configuration that is cluster wide but to enable this particular extension for a particular database I need to go database by database and enter another command that command is pretty simply create tension just add statements it echoes out create extension and now if I take a look at the relations that are available this is one thing that we should know about PG stat statements is it adds a view to each database where it's enabled that allows us access to a particular number of statistics on sequel statements that have been executed on that database so here in organ we have this new view called PG stat statements what does it provide well take a look at PG stat statements the best approach would just be go straight to the documentation so I already got that pulled up and here there's a number of statistics gathered by the module that are listed here in the documentation um everything from total time for a particular statement over every single time is executed it - the number of times it's been called minimum time it takes maximum time mean time these are all in milliseconds by the way storage blocks that are HID whether memory or read from disk and so on so it's very useful how would I actually use this in production right now I could change my table view to be extended to make it easier to see and I'll say select are from eg status statements alright right now there are a number of statements that have been executed and they're all really hitting system catalogues none of these are things that I've directly called most of them been called about three times or so so it's actually artificially bolster that up so we can kind of see what it will look like in a test environment so what I'll do is I'll just write a quick little batch script here on the command line I'll be a select star from employees that little script will execute that command ten times or a hundred times rather so there's one execution and now I'm going to edit the video so you don't have to see the other hundred times I execute it okay so that executed about a hundred times let's do that again but I'm gonna make it a little bit smarter this time I'm gonna have another script that runs 200 times we're gonna look for the count employees and this time so that I don't have to edit the video I'm a smart lease and output - Wow that's what that looks like till it executes that count two hundred times type it correctly it will there we go so now let's go back keep in mind this is what the PG stat statements for you will provide let's take a look at the calls let's take a look at all statements executed in the organ database but let's order them by the number of times they've been called you can see how that could be valuable in a production environment if you want to know heywhat queries are my particular applications running the most what queries or calls are my users executing the most and then we could take a look at you know how long they're taking so we'll do that real quick so I'll log back in and we'll say select change it to extended via select star from eg at statements order by calls descending and notice just like I said previously when I was testing this demonstration out I called these select commands a little bit more so you can see now they're rising to the top I've called them three hundred times that's the particular query that was executed how long did it take well at minimum it took point four milliseconds at maximum took point six seven milliseconds so your average is point five nine how many rows were retrieved well it's just retrieving a single metadata row so only one row per execution let's take a look at the select star from employees that one was executed 300 times takes about six milliseconds at the least seven milliseconds at the max so your average is six point five and then over so many times retrieving all 5,000 rows or so then in total it's returned 1.5 million rows so you can see how this definitely could be valuable in a production environment that's just a quick demo an introduction to PG statements hope you enjoyed it remember to Like and subscribe thanks for watching

Channel: PostgresCourse.com

Views: 1,285

Rating: 5 out of 5

Keywords: postgres, PostgreSQL, postgres extensions, pg_stat_statements, database, SQL, postgres performance, PostgreSQL performance, postgres table statistics, PostgreSQL table statistics

Id: FmtdaZpf318

Length: 10min 2sec (602 seconds)

Published: Wed Apr 01 2020