{"id":2228,"date":"2025-03-04T16:41:27","date_gmt":"2025-03-04T09:41:27","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=2228"},"modified":"2025-03-07T11:18:56","modified_gmt":"2025-03-07T04:18:56","slug":"postgresql-expression-index","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/","title":{"rendered":"PostgreSQL Expression Index"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you&#8217;ll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression, rather than on column data.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='postgresql-expression-index-overview'>PostgreSQL expression index overview <a href=\"#postgresql-expression-index-overview\" class=\"anchor\" id=\"postgresql-expression-index-overview\" title=\"Anchor for PostgreSQL expression index overview\">#<\/a><\/h2>\n\n\n\n<p>When <a href=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-create-index\/\">creating an index<\/a>, you specify one or more columns of a table to include in the index. PostgreSQL will extract values from these columns for creating the index. When querying data based on the indexed columns, PostgreSQL uses the index for fast lookup.<\/p>\n\n\n\n<p>You can create an expression index based on the results of an expression that involves table columns rather than the columns&#8217; data. When you create an expression index, PostgreSQL evaluates the expression and uses the results for indexing.<\/p>\n\n\n\n<p>When you query data from the table using expression, PostgreSQL will utilize the expression index to improve the performance.<\/p>\n\n\n\n<p>To create an expression index, you use the following form of the <code>CREATE INDEX<\/code> statement:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> &#91;index_name]\n<span class=\"hljs-keyword\">ON<\/span> <span class=\"hljs-built_in\">table_name<\/span>(expression);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>In this syntax:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>First, specify the name of the index after the <code>CREATE INDEX<\/code> keywords. The index name is optional.<\/li>\n\n\n\n<li>Second, provide the name of the table you want to create an index on.<\/li>\n\n\n\n<li>Third, define an expression for creating the index.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='creating-an-expression-index-based-on-the-result-of-a-function'>Creating an expression index based on the result of a function <a href=\"#creating-an-expression-index-based-on-the-result-of-a-function\" class=\"anchor\" id=\"creating-an-expression-index-based-on-the-result-of-a-function\" title=\"Anchor for Creating an expression index based on the result of a function\">#<\/a><\/h2>\n\n\n\n<p>We&#8217;ll use the <code>products<\/code> table from the <code>inventory<\/code> database:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"159\" height=\"254\" src=\"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/products.png\" alt=\"PostgreSQL Expression Index\" class=\"wp-image-1051\"\/><\/figure>\n\n\n\n<p>First, create an index for the <code>product_name<\/code> column of the <code>products<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> <span class=\"hljs-keyword\">ON<\/span> products (product_name);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIElOREVYIE9OIHByb2R1Y3RzIChwcm9kdWN0X25hbWUpOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Second, find the products with the name <code>'apple iphone 15'<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">EXPLAIN<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span>\n  product_name,\n  price\n<span class=\"hljs-keyword\">FROM<\/span>\n  products\n<span class=\"hljs-keyword\">WHERE<\/span>\n  LOWER(product_name) = <span class=\"hljs-string\">'apple iphone 15'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=RVhQTEFJTiBTRUxFQ1QgcHJvZHVjdF9uYW1lLCBwcmljZSBGUk9NIHByb2R1Y3RzIFdIRVJFIExPV0VSKHByb2R1Y3RfbmFtZSkgPSAnYXBwbGUgaXBob25lIDE1Jzs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>This query does not utilize the index <code>products_product_name_idx<\/code>.<\/p>\n\n\n\n<p>Third, create an expression index for the <code>products<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> <span class=\"hljs-keyword\">ON<\/span> products (LOWER(product_name));<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIElOREVYIE9OIHByb2R1Y3RzIChMT1dFUihwcm9kdWN0X25hbWUpKTs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>This statement creates a new index with the name <code>products_lower_idx<\/code>.<\/p>\n\n\n\n<p>Finally, run the query that finds products using the <code><a href=\"https:\/\/www.pgtutorial.com\/postgresql-string-functions\/postgresql-lower\/\">LOWER()<\/a><\/code> function:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">EXPLAIN<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span>\n  product_name,\n  price\n<span class=\"hljs-keyword\">FROM<\/span>\n  products\n<span class=\"hljs-keyword\">WHERE<\/span>\n  LOWER(product_name) = <span class=\"hljs-string\">'apple iphone 15'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=RVhQTEFJTiBTRUxFQ1QgcHJvZHVjdF9uYW1lLCBwcmljZSBGUk9NIHByb2R1Y3RzIFdIRVJFIExPV0VSKHByb2R1Y3RfbmFtZSkgPSAnYXBwbGUgaXBob25lIDE1Jzs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">                                   QUERY PLAN\n<span class=\"hljs-comment\">---------------------------------------------------------------------------------<\/span>\n Bitmap Heap Scan <span class=\"hljs-keyword\">on<\/span> products  (<span class=\"hljs-keyword\">cost<\/span>=<span class=\"hljs-number\">4.32<\/span>.<span class=\"hljs-number\">.15<\/span><span class=\"hljs-number\">.09<\/span> <span class=\"hljs-keyword\">rows<\/span>=<span class=\"hljs-number\">5<\/span> width=<span class=\"hljs-number\">17<\/span>)\n   Recheck Cond: (lower((product_name)::<span class=\"hljs-type\">text<\/span>) = <span class=\"hljs-string\">'apple iphone 15'<\/span>::<span class=\"hljs-type\">text<\/span>)\n   -&gt;  Bitmap <span class=\"hljs-keyword\">Index<\/span> Scan <span class=\"hljs-keyword\">on<\/span> products_lower_idx  (<span class=\"hljs-keyword\">cost<\/span>=<span class=\"hljs-number\">0.00<\/span>.<span class=\"hljs-number\">.4<\/span><span class=\"hljs-number\">.31<\/span> <span class=\"hljs-keyword\">rows<\/span>=<span class=\"hljs-number\">5<\/span> width=<span class=\"hljs-number\">0<\/span>)\n         <span class=\"hljs-keyword\">Index<\/span> Cond: (lower((product_name)::<span class=\"hljs-type\">text<\/span>) = <span class=\"hljs-string\">'apple iphone 15'<\/span>::<span class=\"hljs-type\">text<\/span>)\n(<span class=\"hljs-number\">4<\/span> <span class=\"hljs-keyword\">rows<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output indicates that the query uses the expression index to search for the product.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='creating-an-expression-index-based-on-the-result-of-an-expression'>Creating an expression index based on the result of an expression <a href=\"#creating-an-expression-index-based-on-the-result-of-an-expression\" class=\"anchor\" id=\"creating-an-expression-index-based-on-the-result-of-an-expression\" title=\"Anchor for Creating an expression index based on the result of an expression\">#<\/a><\/h2>\n\n\n\n<p>First, create an expression index based on the values of safety stocks:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">INDEX<\/span> <span class=\"hljs-keyword\">ON<\/span> products ((price * safety_stock));<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=Q1JFQVRFIElOREVYIE9OIHByb2R1Y3RzICgocHJpY2UgKiBzYWZldHlfc3RvY2spKTs%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>This statement creates an expression index with the name <code>products_expr_idx<\/code>.<\/p>\n\n\n\n<p class=\"note\">Note that you have to place the expression within parentheses. So, there are two parentheses after the table name <code>((expression))<\/code>.<\/p>\n\n\n\n<p>Second, query the products by the safety stock values:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\"><span class=\"hljs-keyword\">EXPLAIN<\/span>\n<span class=\"hljs-keyword\">SELECT<\/span>\n  product_name,\n  price,\n  safety_stock\n<span class=\"hljs-keyword\">FROM<\/span>\n  products\n<span class=\"hljs-keyword\">WHERE<\/span>\n  price * safety_stock &gt; <span class=\"hljs-number\">1000<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p><a href=\"https:\/\/www.pgtutorial.com\/playground\/?q=RVhQTEFJTiBTRUxFQ1QgcHJvZHVjdF9uYW1lLCBwcmljZSwgc2FmZXR5X3N0b2NrIEZST00gcHJvZHVjdHMgV0hFUkUgcHJpY2UgKiBzYWZldHlfc3RvY2sgPiAxMDAwOw%3D%3D\" target=\"_blank\" rel=\"noreferrer noopener\">Try it<\/a><\/p>\n\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"PostgreSQL SQL dialect and PL\/pgSQL\" data-shcb-language-slug=\"pgsql\"><span><code class=\"hljs language-pgsql\">                                    QUERY PLAN\n<span class=\"hljs-comment\">-----------------------------------------------------------------------------------<\/span>\n Bitmap Heap Scan <span class=\"hljs-keyword\">on<\/span> products  (<span class=\"hljs-keyword\">cost<\/span>=<span class=\"hljs-number\">10.93<\/span>.<span class=\"hljs-number\">.29<\/span><span class=\"hljs-number\">.91<\/span> <span class=\"hljs-keyword\">rows<\/span>=<span class=\"hljs-number\">342<\/span> width=<span class=\"hljs-number\">21<\/span>)\n   Recheck Cond: ((price * (safety_stock)::<span class=\"hljs-type\">numeric<\/span>) &gt; <span class=\"hljs-string\">'1000'<\/span>::<span class=\"hljs-type\">numeric<\/span>)\n   -&gt;  Bitmap <span class=\"hljs-keyword\">Index<\/span> Scan <span class=\"hljs-keyword\">on<\/span> products_expr_idx  (<span class=\"hljs-keyword\">cost<\/span>=<span class=\"hljs-number\">0.00<\/span>.<span class=\"hljs-number\">.10<\/span><span class=\"hljs-number\">.84<\/span> <span class=\"hljs-keyword\">rows<\/span>=<span class=\"hljs-number\">342<\/span> width=<span class=\"hljs-number\">0<\/span>)\n         <span class=\"hljs-keyword\">Index<\/span> Cond: ((price * (safety_stock)::<span class=\"hljs-type\">numeric<\/span>) &gt; <span class=\"hljs-string\">'1000'<\/span>::<span class=\"hljs-type\">numeric<\/span>)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PostgreSQL SQL dialect and PL\/pgSQL<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">pgsql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output indicates that the query uses the expression index.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id='when-to-use-expression-indexes'>When to use expression indexes <a href=\"#when-to-use-expression-indexes\" class=\"anchor\" id=\"when-to-use-expression-indexes\" title=\"Anchor for When to use expression indexes\">#<\/a><\/h2>\n\n\n\n<p>In practice, you find the expression indexes useful in the following cases:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Case-sensitive searches.<\/li>\n\n\n\n<li>Filtering and sorting rows based on an expression.<\/li>\n\n\n\n<li>Filtering rows based on parts of a date or time.<\/li>\n\n\n\n<li>Filtering JSON data using the <code>JSONB_EXTRACT_PATH<\/code> function.<\/li>\n\n\n\n<li>Filtering array data using the <code>ARRAY_LENGTH<\/code> function.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='summary'>Summary <a href=\"#summary\" class=\"anchor\" id=\"summary\" title=\"Anchor for Summary\">#<\/a><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use expression indexes to optimize queries that involve expressions.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\" id='quiz'>Quiz <a href=\"#quiz\" class=\"anchor\" id=\"quiz\" title=\"Anchor for Quiz\">#<\/a><\/h2>\n\n\n\n<iframe loading=\"lazy\"\n  name=\"quiz\"\n  src=\"\/quiz\/?quiz=expression-index\"\n  height=\"700\"\n  width=\"600\"\n  class=\"iframe\"\n><\/iframe>\n\n\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful ?<\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"2228\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL Expression Index\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"2228\"\n\t\t\t\tdata-post-url=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/\"\n\t\t\t\tdata-post-title=\"PostgreSQL Expression Index\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you&#8217;ll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression, rather than on column data. PostgreSQL expression index overview # When creating an index, you specify one or more columns of a table to include in the index. PostgreSQL will extract values from [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":13,"menu_order":86,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-2228","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>PostgreSQL Expression Index<\/title>\n<meta name=\"description\" content=\"You&#039;ll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression\" \/>\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.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL Expression Index\" \/>\n<meta property=\"og:description\" content=\"You&#039;ll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/\" \/>\n<meta property=\"og:site_name\" content=\"PostgreSQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2025-03-07T04:18:56+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/products.png\" \/>\n\t<meta property=\"og:image:width\" content=\"159\" \/>\n\t<meta property=\"og:image:height\" content=\"254\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/\",\"name\":\"PostgreSQL Expression Index\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.pgtutorial.com\\\/wp-content\\\/uploads\\\/2024\\\/12\\\/products.png\",\"datePublished\":\"2025-03-04T09:41:27+00:00\",\"dateModified\":\"2025-03-07T04:18:56+00:00\",\"description\":\"You'll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/wp-content\\\/uploads\\\/2024\\\/12\\\/products.png\",\"contentUrl\":\"https:\\\/\\\/www.pgtutorial.com\\\/wp-content\\\/uploads\\\/2024\\\/12\\\/products.png\",\"width\":159,\"height\":254,\"caption\":\"PostgreSQL Expression Index\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/postgresql-tutorial\\\/postgresql-expression-index\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL Tutorial\",\"item\":\"https:\\\/\\\/www.pgtutorial.com\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"PostgreSQL Expression Index\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.pgtutorial.com\\\/#website\",\"url\":\"https:\\\/\\\/www.pgtutorial.com\\\/\",\"name\":\"PostgreSQL Tutorial\",\"description\":\"Learn PostgreSQL from Scratch\",\"alternateName\":\"PostgreSQL\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.pgtutorial.com\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL Expression Index","description":"You'll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression","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.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL Expression Index","og_description":"You'll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression","og_url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/","og_site_name":"PostgreSQL Tutorial","article_modified_time":"2025-03-07T04:18:56+00:00","og_image":[{"width":159,"height":254,"url":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/products.png","type":"image\/png"}],"twitter_card":"summary_large_image","twitter_misc":{"Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/","url":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/","name":"PostgreSQL Expression Index","isPartOf":{"@id":"https:\/\/www.pgtutorial.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/#primaryimage"},"image":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/#primaryimage"},"thumbnailUrl":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/products.png","datePublished":"2025-03-04T09:41:27+00:00","dateModified":"2025-03-07T04:18:56+00:00","description":"You'll learn how to use PostgreSQL expression indexes to create indexes based on the results of an expression","breadcrumb":{"@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/#primaryimage","url":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/products.png","contentUrl":"https:\/\/www.pgtutorial.com\/wp-content\/uploads\/2024\/12\/products.png","width":159,"height":254,"caption":"PostgreSQL Expression Index"},{"@type":"BreadcrumbList","@id":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-expression-index\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL Tutorial","item":"https:\/\/www.pgtutorial.com\/"},{"@type":"ListItem","position":3,"name":"PostgreSQL Expression Index"}]},{"@type":"WebSite","@id":"https:\/\/www.pgtutorial.com\/#website","url":"https:\/\/www.pgtutorial.com\/","name":"PostgreSQL Tutorial","description":"Learn PostgreSQL from Scratch","alternateName":"PostgreSQL","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.pgtutorial.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/2228","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/comments?post=2228"}],"version-history":[{"count":3,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/2228\/revisions"}],"predecessor-version":[{"id":2261,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/2228\/revisions\/2261"}],"up":[{"embeddable":true,"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/pages\/13"}],"wp:attachment":[{"href":"https:\/\/www.pgtutorial.com\/wp-json\/wp\/v2\/media?parent=2228"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}