{"id":16699,"date":"2021-09-27T08:39:51","date_gmt":"2021-09-27T06:39:51","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/"},"modified":"2021-09-27T08:39:51","modified_gmt":"2021-09-27T06:39:51","slug":"postgresql-indexes-and-operators","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/","title":{"rendered":"PostgreSQL indexes and operators"},"content":{"rendered":"

Creating a standard B-tree index in PostgreSQL is nothing special and usually we do it without thinking about what happens in the background. For the standard data types like e.g. int or text we do not need to know anything about operators<\/a> or operator classes<\/a>. In those cases the standard operator class for the data type is applied and all is fine. But sometimes we need to know about the possibilities PostgreSQL comes with, and this is the topic of this post.<\/p>\n

<\/p>\n

As usual, let’s start with a simple setup:<\/p>\n

\npostgres=# create table t1 ( a int, b text );\nCREATE TABLE\npostgres=# insert into t1 select i, md5(i::text) from generate_series(1,1000000) i;\nINSERT 0 1000000\npostgres=# select * from t1 limit 5;\n a |                b                 \n---+----------------------------------\n 1 | c4ca4238a0b923820dcc509a6f75849b\n 2 | c81e728d9d4c2f636f067f89cc14862c\n 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3\n 4 | a87ff679a2f3e71d9181a67b7542122c\n 5 | e4da3b7fbbce2345d7772b0674a318d5\n(5 rows)\n<\/pre>\n
We now have a simple table with two columns and can create standard B-tree indexes like this:<\/p>\n
\npostgres=# create index i1 on t1(a);\nCREATE INDEX\npostgres=# create index i2 on t1(b);\nCREATE INDEX\npostgres=# d t1\n                 Table \"public.t1\"\n Column |  Type   | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a      | integer |           |          | \n b      | text    |           |          | \nIndexes:\n    \"i1\" btree (a)\n    \"i2\" btree (b)\n<\/pre>\n
These indexes work perfectly fine, when you ask for rows like e.g. this:<\/p>\n
\npostgres=# explain select * from t1 where a = 5;\n                          QUERY PLAN                          \n--------------------------------------------------------------\n Index Scan using i1 on t1  (cost=0.42..8.44 rows=1 width=37)\n   Index Cond: (a = 5)\n(2 rows)\n\npostgres=# explain select * from t1 where b = 'e4da3b7fbbce2345d7772b0674a318d5';\n                          QUERY PLAN                          \n--------------------------------------------------------------\n Index Scan using i2 on t1  (cost=0.42..8.44 rows=1 width=37)\n   Index Cond: (b = 'e4da3b7fbbce2345d7772b0674a318d5'::text)\n(2 rows)\n<\/pre>\n
But how can we know which operators are supported by a specific index and data type? This can easily be answered by asking the PostgreSQL catalog:<\/p>\n
\npostgres=# select am.amname AS index_method\n                , opc.opcname AS opclass_name\n                , opc.opcintype::regtype AS indexed_type\n                , opc.opcdefault AS is_default\n             from pg_am am\n                , pg_opclass opc\n            where opc.opcmethod = am.oid \n              and am.amname = 'btree'\n         order by index_method, opclass_name;\n index_method |    opclass_name     |        indexed_type         | is_default \n--------------+---------------------+-----------------------------+------------\n btree        | array_ops           | anyarray                    | t\n btree        | bit_ops             | bit                         | t\n btree        | bool_ops            | boolean                     | t\n btree        | bpchar_ops          | character                   | t\n btree        | bpchar_pattern_ops  | character                   | f\n btree        | bytea_ops           | bytea                       | t\n btree        | char_ops            | \"char\"                      | t\n btree        | cidr_ops            | inet                        | f\n btree        | date_ops            | date                        | t\n btree        | enum_ops            | anyenum                     | t\n btree        | float4_ops          | real                        | t\n btree        | float8_ops          | double precision            | t\n btree        | inet_ops            | inet                        | t\n btree        | int2_ops            | smallint                    | t\n btree        | int4_ops            | integer                     | t\n btree        | int8_ops            | bigint                      | t\n btree        | interval_ops        | interval                    | t\n btree        | jsonb_ops           | jsonb                       | t\n btree        | macaddr8_ops        | macaddr8                    | t\n btree        | macaddr_ops         | macaddr                     | t\n btree        | money_ops           | money                       | t\n btree        | name_ops            | name                        | t\n btree        | numeric_ops         | numeric                     | t\n btree        | oid_ops             | oid                         | t\n btree        | oidvector_ops       | oidvector                   | t\n btree        | pg_lsn_ops          | pg_lsn                      | t\n btree        | range_ops           | anyrange                    | t\n btree        | record_image_ops    | record                      | f\n btree        | record_ops          | record                      | t\n btree        | text_ops            | text                        | t\n btree        | text_pattern_ops    | text                        | f\n btree        | tid_ops             | tid                         | t\n btree        | time_ops            | time without time zone      | t\n btree        | timestamp_ops       | timestamp without time zone | t\n btree        | timestamptz_ops     | timestamp with time zone    | t\n btree        | timetz_ops          | time with time zone         | t\n btree        | tsquery_ops         | tsquery                     | t\n btree        | tsvector_ops        | tsvector                    | t\n btree        | uuid_ops            | uuid                        | t\n btree        | varbit_ops          | bit varying                 | t\n btree        | varchar_ops         | text                        | f\n btree        | varchar_pattern_ops | text                        | f\n btree        | xid8_ops            | xid8                        | t\n(43 rows)\n<\/pre>\n
A B-tree index, as you can see above, supports all the data types listed in the “indexed_type” column. If you want to know which operators are supported by a operator family you can also ask the PostgreSQL catalog, e.g. for integer and text:<\/p>\n
\npostgres=# select am.amname as index_method\n                , opf.opfname as opfamily_name\n                , amop.amopopr::regoperator as opfamily_operator\n           from pg_am am\n              , pg_opfamily opf\n              , pg_amop amop\n          where opf.opfmethod = am.oid \n            and amop.amopfamily = opf.oid \n            and am.amname = 'btree'\n            and opf.opfname in ('integer_ops','text_ops')\n          order by 1,2,3;\n index_method | opfamily_name |   opfamily_operator   \n--------------+---------------+-----------------------\n btree        | integer_ops   | =(integer,bigint)\n btree        | integer_ops   | (integer,bigint)\n btree        | integer_ops   | =(integer,bigint)\n btree        | integer_ops   | =(smallint,smallint)\n btree        | integer_ops   | <(smallint,smallint)\n btree        | integer_ops   | =(integer,integer)\n btree        | integer_ops   | <(integer,integer)\n btree        | integer_ops   | =(bigint,bigint)\n btree        | integer_ops   | (bigint,bigint)\n btree        | integer_ops   | =(bigint,bigint)\n btree        | integer_ops   | =(bigint,integer)\n btree        | integer_ops   | (bigint,integer)\n btree        | integer_ops   | =(bigint,integer)\n btree        | integer_ops   | >(smallint,smallint)\n btree        | integer_ops   | >(integer,integer)\n btree        | integer_ops   | <=(smallint,smallint)\n btree        | integer_ops   | =(smallint,smallint)\n btree        | integer_ops   | >=(integer,integer)\n btree        | integer_ops   | =(smallint,integer)\n btree        | integer_ops   | =(integer,smallint)\n btree        | integer_ops   | <(smallint,integer)\n btree        | integer_ops   | (smallint,integer)\n btree        | integer_ops   | >(integer,smallint)\n btree        | integer_ops   | <=(smallint,integer)\n btree        | integer_ops   | =(smallint,integer)\n btree        | integer_ops   | >=(integer,smallint)\n btree        | integer_ops   | =(smallint,bigint)\n btree        | integer_ops   | (smallint,bigint)\n btree        | integer_ops   | =(smallint,bigint)\n btree        | integer_ops   | =(bigint,smallint)\n btree        | integer_ops   | (bigint,smallint)\n btree        | integer_ops   | =(bigint,smallint)\n btree        | text_ops      | =(name,name)\n btree        | text_ops      | =(text,text)\n btree        | text_ops      | =(name,text)\n btree        | text_ops      | <(name,text)\n btree        | text_ops      | =(name,text)\n btree        | text_ops      | >(name,text)\n btree        | text_ops      | =(text,name)\n btree        | text_ops      | <(text,name)\n btree        | text_ops      | =(text,name)\n btree        | text_ops      | >(text,name)\n btree        | text_ops      | <(name,name)\n btree        | text_ops      | (name,name)\n btree        | text_ops      | >=(name,name)\n btree        | text_ops      | <(text,text)\n btree        | text_ops      | (text,text)\n btree        | text_ops      | >=(text,text)\n(65 rows)\n<\/pre>\n
Using a B-tree index for these data types the index will support “>”, “=”, “<=" and "=". This can be annoying if you want to query a text column like this, as the index cannot support this:<\/p>\n
\npostgres=# explain select * from t1 where b like 'e4da3b7fbbce2345d77%';\n                              QUERY PLAN                               \n-----------------------------------------------------------------------\n Gather  (cost=1000.00..14552.33 rows=100 width=37)\n   Workers Planned: 2\n   ->  Parallel Seq Scan on t1  (cost=0.00..13542.33 rows=42 width=37)\n         Filter: (b ~~ 'e4da3b7fbbce2345d77%'::text)\n(4 rows)\n<\/pre>\n
For “text” PostgreSQL comes with another build-in operator class you can use exactly for this purpose:<\/p>\n
\npostgres=# select am.amname AS index_method\n                , opc.opcname AS opclass_name\n                , opc.opcintype::regtype AS indexed_type\n                , opc.opcdefault AS is_default\n             from pg_am am\n                , pg_opclass opc\n            where opc.opcmethod = am.oid \n              and am.amname = 'btree' \n              and opc.opcintype::regtype::text = 'text'\n         order by index_method, opclass_name;\n index_method |    opclass_name     | indexed_type | is_default \n--------------+---------------------+--------------+------------\n btree        | text_ops            | text         | t\n btree        | text_pattern_ops    | text         | f\n btree        | varchar_ops         | text         | f\n btree        | varchar_pattern_ops | text         | f\n(4 rows)\n<\/pre>\n
The default for “text ” is “text_ops”, but there is also “text_pattern_ops”, which you can use like this:<\/p>\n
\npostgres=# select am.amname as index_method\n                , opf.opfname as opfamily_name\n                , amop.amopopr::regoperator as opfamily_operator\n             from pg_am am\n                , pg_opfamily opf\n                , pg_amop amop\n            where opf.opfmethod = am.oid \n              and amop.amopfamily = opf.oid \n              and am.amname = 'btree'\n              and opf.opfname = 'text_pattern_ops'\n        order by 1,2,3;\n index_method |  opfamily_name   | opfamily_operator \n--------------+------------------+-------------------\n btree        | text_pattern_ops | =(text,text)\n btree        | text_pattern_ops | ~<~(text,text)\n btree        | text_pattern_ops | ~=~(text,text)\n btree        | text_pattern_ops | ~>~(text,text)\n(5 rows)\n\npostgres=# create index i3 on t1(b text_pattern_ops);\nCREATE INDEX\npostgres=# explain select * from t1 where b like 'e4da3b7fbbce2345d77%';\n                                          QUERY PLAN                                          \n----------------------------------------------------------------------------------------------\n Index Scan using i3 on t1  (cost=0.42..8.45 rows=100 width=37)\n   Index Cond: ((b ~>=~ 'e4da3b7fbbce2345d77'::text) AND (b ~<~ 'e4da3b7fbbce2345d78'::text))\n   Filter: (b ~~ 'e4da3b7fbbce2345d77%'::text)\n(3 rows)\n<\/pre>\n
Here is more detailed description in the documentation<\/a>. Finally, if you know what you need, you can create your own operators, operator families and operator classes:<\/p>\n
\npostgres=# h create operator\nCommand:     CREATE OPERATOR\nDescription: define a new operator\nSyntax:\nCREATE OPERATOR name (\n    {FUNCTION|PROCEDURE} = function_name\n    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]\n    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]\n    [, RESTRICT = res_proc ] [, JOIN = join_proc ]\n    [, HASHES ] [, MERGES ]\n)\n\nURL: https:\/\/www.postgresql.org\/docs\/13\/sql-createoperator.html\n\npostgres=# h create operator family\nCommand:     CREATE OPERATOR FAMILY\nDescription: define a new operator family\nSyntax:\nCREATE OPERATOR FAMILY name USING index_method\n\nURL: https:\/\/www.postgresql.org\/docs\/13\/sql-createopfamily.html\n\npostgres=# h create operator class\nCommand:     CREATE OPERATOR CLASS\nDescription: define a new operator class\nSyntax:\nCREATE OPERATOR CLASS name [ DEFAULT ] FOR TYPE data_type\n  USING index_method [ FAMILY family_name ] AS\n  {  OPERATOR strategy_number operator_name [ ( op_type, op_type ) ] [ FOR SEARCH | FOR ORDER BY sort_family_name ]\n   | FUNCTION support_number [ ( op_type [ , op_type ] ) ] function_name ( argument_type [, ...] )\n   | STORAGE storage_type\n  } [, ... ]\n\nURL: https:\/\/www.postgresql.org\/docs\/13\/sql-createopclass.html\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Creating a standard B-tree index in PostgreSQL is nothing special and usually we do it without thinking about what happens in the background. For the standard data types like e.g. int or text we do not need to know anything about operators or operator classes. In those cases the standard operator class for the data […]<\/p>\n","protected":false},"author":29,"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-16699","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nPostgreSQL indexes and operators - 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\/postgresql-indexes-and-operators\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL indexes and operators\" \/>\n<meta property=\"og:description\" content=\"Creating a standard B-tree index in PostgreSQL is nothing special and usually we do it without thinking about what happens in the background. For the standard data types like e.g. int or text we do not need to know anything about operators or operator classes. In those cases the standard operator class for the data […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-09-27T06:39:51+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 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\/postgresql-indexes-and-operators\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL indexes and operators\",\"datePublished\":\"2021-09-27T06:39:51+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/\"},\"wordCount\":298,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/\",\"name\":\"PostgreSQL indexes and operators - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2021-09-27T06:39:51+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL indexes and operators\"}]},{\"@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\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL indexes and operators - 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\/postgresql-indexes-and-operators\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL indexes and operators","og_description":"Creating a standard B-tree index in PostgreSQL is nothing special and usually we do it without thinking about what happens in the background. For the standard data types like e.g. int or text we do not need to know anything about operators or operator classes. In those cases the standard operator class for the data […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/","og_site_name":"dbi Blog","article_published_time":"2021-09-27T06:39:51+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL indexes and operators","datePublished":"2021-09-27T06:39:51+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/"},"wordCount":298,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/","name":"PostgreSQL indexes and operators - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2021-09-27T06:39:51+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-indexes-and-operators\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL indexes and operators"}]},{"@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\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16699","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\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=16699"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/16699\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=16699"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=16699"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=16699"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=16699"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}