{"id":11043,"date":"2018-03-26T09:13:32","date_gmt":"2018-03-26T07:13:32","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/"},"modified":"2018-03-26T09:13:32","modified_gmt":"2018-03-26T07:13:32","slug":"when-you-execute-the-same-statement-thousands-of-times-prepare-it","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/","title":{"rendered":"When you execute the same statement thousands of times -> prepare it"},"content":{"rendered":"

Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare<\/a> statement. Using that you can avoid parsing of statements and only planning and executing will happen. Lets see how that works.<\/p>\n

<\/p>\n

To generate some sample data I used pgbench<\/a> with a scale factor of 100 which gives me 10’000’000 rows in the pgbench_accounts table:<\/p>\n

\npostgres@pgbox:\/home\/postgres\/ [PGDEV] pgbench -i -s 100 bench\npostgres@pgbox:\/home\/postgres\/ [PGDEV] psql bench\n\npsql (11devel)\nType \"help\" for help.\n\nbench=# select count(*) from pgbench_accounts;\n  count   \n----------\n 10000000\n(1 row)\n\nbench=# d pgbench_accounts\n              Table \"public.pgbench_accounts\"\n  Column  |     Type      | Collation | Nullable | Default \n----------+---------------+-----------+----------+---------\n aid      | integer       |           | not null | \n bid      | integer       |           |          | \n abalance | integer       |           |          | \n filler   | character(84) |           |          | \nIndexes:\n    \"pgbench_accounts_pkey\" PRIMARY KEY, btree (aid)\n<\/pre>\n
Lets assume we have the following simple query:<\/p>\n
\nbench=# select count(*) from pgbench_accounts where aid = 11111;\n count \n-------\n     1\n(1 row)\n<\/pre>\n
As said at the beginning of this post PostgreSQL will need to parse that statement. Using explain<\/a> with the right options you are able to see how much time was spend on planning:<\/p>\n
\nbench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;\n                                                                    QUERY PLAN                                                                     \n---------------------------------------------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.060..0.063 rows=1 loops=1)\n   Buffers: shared hit=4\n   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.034..0.039 rows=1 loops=1)\n         Index Cond: (aid = 11111)\n         Heap Fetches: 0\n         Buffers: shared hit=4\n Planning Time: 0.150 ms\n Execution Time: 0.133 ms\n(8 rows)\n<\/pre>\n
Planning this statement takes more time then executing it. Now assume you want to execute that very same statement one thousand times:<\/p>\n
\nbench=# t\nbench=# select 'select count(*) from pgbench_accounts where aid = 11111;' from generate_series(1,1000) i; g test.sql\nbench=# ! cat test.sql | head\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n select count(*) from pgbench_accounts where aid = 11111;\n...\n<\/pre>\n
When we execute that we force PostgreSQL to parse all of those 1000 statements:<\/p>\n
\nbench=# timing\nTiming is on.\nbench=# ! time psql -f test.sql bench\n\nreal\t0m0.148s\nuser\t0m0.031s\nsys\t0m0.015s\n<\/pre>\n
What you can do to avoid that is to prepare the statement:<\/p>\n
\nbench=# prepare tt as select count(*) from pgbench_accounts where aid = 11111;\nPREPARE\n<\/pre>\n
Once it is prepared you can execute it:<\/p>\n
\nbench=# execute tt;\n count \n-------\n     1\n(1 row)\n<\/pre>\n
… and you can also explain it:<\/p>\n
\nbench=# explain(analyze,buffers) execute tt;\n                                                                    QUERY PLAN                                                                     \n---------------------------------------------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.066..0.069 rows=1 loops=1)\n   Buffers: shared hit=4\n   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.037..0.043 rows=1 loops=1)\n         Index Cond: (aid = 11111)\n         Heap Fetches: 0\n         Buffers: shared hit=4\n Planning Time: 0.021 ms\n Execution Time: 0.269 ms\n(8 rows)\n<\/pre>\n
You’ll notice that the planning time went down quite a lot compared to the not prepared statement:<\/p>\n
\nbench=# explain (analyze,buffers) select count(*) from pgbench_accounts where aid = 11111;\n                                                                    QUERY PLAN                                                                     \n---------------------------------------------------------------------------------------------------------------------------------------------------\n Aggregate  (cost=4.46..4.46 rows=1 width=8) (actual time=0.167..0.172 rows=1 loops=1)\n   Buffers: shared hit=4\n   ->  Index Only Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.43..4.45 rows=1 width=0) (actual time=0.030..0.037 rows=1 loops=1)\n         Index Cond: (aid = 11111)\n         Heap Fetches: 0\n         Buffers: shared hit=4\n Planning Time: 0.167 ms\n Execution Time: 0.248 ms\n(8 rows)\n<\/pre>\n
When you do that one thousand times now:<\/p>\n
\nbench=# t\nbench=# select 'execute tt;' from generate_series(1,1000) i; g test.sql\nbench=# ! sed -i '1s\/^\/prepare tt as select count(*) from pgbench_accounts where aid = 11111;\/' test.sql\nbench=# ! time psql -f test.sql bench\n\nreal\t0m0.095s\nuser\t0m0.031s\nsys\t0m0.010s\n<\/pre>\n
… execution time will come down. It is not much in that simple case but this is due to the simple statement. Btw: As prepared statements only life for the time of the session the sed command adds the prepare statement to the top of file, and preparing takes time as well. Without that execution time would be even less.<\/p>\n
When your values change in the where clause you can do it like this:<\/p>\n
\nbench=# prepare tt as select count(*) from pgbench_accounts where aid = $1;\nPREPARE\nTime: 0.571 ms\nbench=# execute tt (123);\n     1\n<\/pre>\n
For completeness: When you want to get rid of a prepared statement without losing your session there is deallocate<\/a>:<\/p>\n
\nbench=# deallocate tt;\nDEALLOCATE\nTime: 0.623 ms\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing […]<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-11043","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nWhen you execute the same statement thousands of times -> prepare it - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"When you execute the same statement thousands of times -> prepare it\" \/>\n<meta property=\"og:description\" content=\"Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2018-03-26T07:13:32+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"When you execute the same statement thousands of times -> prepare it\",\"datePublished\":\"2018-03-26T07:13:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/\"},\"wordCount\":324,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/\",\"name\":\"When you execute the same statement thousands of times -> prepare it - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2018-03-26T07:13:32+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"When you execute the same statement thousands of times -> prepare it\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g\",\"caption\":\"Open source Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"When you execute the same statement thousands of times -> prepare it - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/","og_locale":"en_US","og_type":"article","og_title":"When you execute the same statement thousands of times -> prepare it","og_description":"Every time you send a SQL statement to PostgreSQL it must be parsed. Parsing is fast, yes, but parsing the same statement a thousand times can quickly sum up to quite some time the database could spend for something else. To avoid that PostgreSQL comes with the prepare statement. Using that you can avoid parsing […]","og_url":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/","og_site_name":"dbi Blog","article_published_time":"2018-03-26T07:13:32+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"When you execute the same statement thousands of times -> prepare it","datePublished":"2018-03-26T07:13:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/"},"wordCount":324,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/","url":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/","name":"When you execute the same statement thousands of times -> prepare it - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2018-03-26T07:13:32+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/when-you-execute-the-same-statement-thousands-of-times-prepare-it\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"When you execute the same statement thousands of times -> prepare it"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/eb4fb12e386e8c41fdef0733e8114594cf2653e4f55e9fa2161442b8eaf3f657?s=96&d=mm&r=g","caption":"Open source Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11043","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=11043"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/11043\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=11043"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=11043"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=11043"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=11043"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}