{"id":10335,"date":"2017-07-12T16:26:29","date_gmt":"2017-07-12T14:26:29","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/"},"modified":"2017-07-12T16:26:29","modified_gmt":"2017-07-12T14:26:29","slug":"can-i-do-it-with-postgresql-15-invisible-indexes","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/","title":{"rendered":"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes"},"content":{"rendered":"

It has been quite a while since the last post in this series. Today we’ll look at what you know from Oracle as: Invisible indexes<\/a>. In case you wonder what they might be useful for: Imagine you want to test if an index would benefit one or more queries without affecting the production workload. In other words: Wouldn’t it be cool to create an index but somehow tell the optimizer not to use it for the ongoing queries? This is what invisible indexes are about: Create an index which you believe should improve performance for one or more queries but at the same step make sure that it is not taken into account when the query plan<\/a> is generated and then executed. The bad news is: This is not possible in PostgreSQL core. The good news is: There is an extension which does exactly this<\/a>.<\/p>\n

<\/p>\n

The extension is called hypopg and is available via github<\/a>. The readme states that it works on all PostgreSQL versions starting with 9.2, so lets try it with PostgreSQL 10 Beta1<\/a>.<\/p>\n

\npostgres@pgbox:\/home\/postgres\/ [PG10B] psql -X postgres\npsql (10beta1 dbi services build)\nType \"help\" for help.\n\npostgres=# select version();\n                                                            version                                                            \n-------------------------------------------------------------------------------------------------------------------------------\n PostgreSQL 10beta1 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit\n(1 row)\n\npostgres=# \n<\/pre>\n
Getting the extension downloaded, compiled and installed is straight forward:<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PG10B] wget https:\/\/github.com\/dalibo\/hypopg\/archive\/master.zip\npostgres@pgbox:\/home\/postgres\/ [PG10B] unzip master.zip \npostgres@pgbox:\/home\/postgres\/ [PG10B] cd hypopg-master\/\npostgres@pgbox:\/home\/postgres\/hypopg-master\/ [PG10B] make install\npostgres@pgbox:\/home\/postgres\/hypopg-master\/ [PG10B] psql -X -c \"create extension hypopg\" postgres\nCREATE EXTENSION\npostgres@pgbox:\/home\/postgres\/hypopg-master\/ [PG10B] psql -X -c \"dx\" postgres\n                     List of installed extensions\n  Name   | Version  |   Schema   |             Description             \n---------+----------+------------+-------------------------------------\n hypopg  | 1.1.0dev | public     | Hypothetical indexes for PostgreSQL\n plpgsql | 1.0      | pg_catalog | PL\/pgSQL procedural language\n(2 rows)\n<\/pre>\n
Here we go, all fine until now and we should be ready to use it. Obviously we need a table and some data to test with:<\/p>\n
\npostgres@pgbox:\/home\/postgres\/ [PG10B] psql -X postgres\npsql (10beta1 dbi services build)\nType \"help\" for help.\n\npostgres=# ! cat a.sql\ndrop table if exists t1;\ncreate table t1 ( a int );\nwith generator as \n ( select a.*\n     from generate_series ( 1, 5000000 ) a\n    order by random()\n )\ninsert into t1 ( a ) \n     select a\n       from generator;\npostgres=# i a.sql\nDROP TABLE\nCREATE TABLE\nINSERT 0 5000000\npostgres=# analyze t1;\nANALYZE\npostgres=# select * from pg_size_pretty ( pg_total_relation_size ('t1') );\n pg_size_pretty \n----------------\n 173 MB\n(1 row)\n<\/pre>\n
So now we have a table containing some data. The only choice PostgreSQL has to fetch one or more rows is to use a sequential scan (which is a full table scan in Oracle):<\/p>\n
\npostgres=# explain select * from t1 where a = 5;\n                             QUERY PLAN                              \n---------------------------------------------------------------------\n Gather  (cost=1000.00..49165.77 rows=1 width=4)\n   Workers Planned: 2\n   ->  Parallel Seq Scan on t1  (cost=0.00..48165.67 rows=1 width=4)\n         Filter: (a = 5)\n(4 rows)\n<\/pre>\n
Although PostgreSQL already knows that only one row needs to be returned (rows=1) it still needs to read the whole table. Lets look at how that looks like when we really execute the query by using “explain (analyze)”:<\/p>\n
\npostgres=# explain (analyze) select * from t1 where a = 5;\n                                                    QUERY PLAN                                                     \n-------------------------------------------------------------------------------------------------------------------\n Gather  (cost=1000.00..49165.77 rows=1 width=4) (actual time=133.292..133.839 rows=1 loops=1)\n   Workers Planned: 2\n   Workers Launched: 2\n   ->  Parallel Seq Scan on t1  (cost=0.00..48165.67 rows=1 width=4) (actual time=110.446..124.888 rows=0 loops=3)\n         Filter: (a = 5)\n         Rows Removed by Filter: 1666666\n Planning time: 0.055 ms\n Execution time: 135.465 ms\n(8 rows)\n<\/pre>\n
What kicked in here is parallel query<\/a> which is available since PostgreSQL 9.6 but this is not really important for the scope of this post. Coming back to the invisible or hypothetical indexes: Having the extension installed we can now do something like this:<\/p>\n
\npostgres=# SELECT * FROM hypopg_create_index('CREATE INDEX ON t1 (a)');\n indexrelid |     indexname     \n------------+-------------------\n      16399 | btree_t1_a\n(1 row)\n\npostgres=# select * from pg_size_pretty ( pg_total_relation_size ('t1') );\n pg_size_pretty \n----------------\n 173 MB\n(1 row)\n<\/pre>\n
What this did is to create a hypothetical index but without consuming any space (pg_total_relation_size<\/a> counts the indexes as well), so it is pretty fast. What happens to our query now?<\/p>\n
\npostgres=# explain select * from t1 where a = 5;\n                                   QUERY PLAN                                    \n---------------------------------------------------------------------------------\n Index Only Scan using btree_t1_a on t1  (cost=0.06..8.07 rows=1 width=4)\n   Index Cond: (a = 5)\n(2 rows)\n<\/pre>\n
Quite cool, the index is really getting used and we did not consume any resources for the index itself. Could be a good index to implement. What you need to know is, that this does not work for “explain analyze” as this really executes the query (and we do not really have an index on disk):<\/p>\n
\npostgres=# explain (analyze) select * from t1 where a = 5;\n                                                    QUERY PLAN                                                     \n-------------------------------------------------------------------------------------------------------------------\n Gather  (cost=1000.00..49165.77 rows=1 width=4) (actual time=76.247..130.235 rows=1 loops=1)\n   Workers Planned: 2\n   Workers Launched: 2\n   ->  Parallel Seq Scan on t1  (cost=0.00..48165.67 rows=1 width=4) (actual time=106.861..124.252 rows=0 loops=3)\n         Filter: (a = 5)\n         Rows Removed by Filter: 1666666\n Planning time: 0.043 ms\n Execution time: 131.866 ms\n(8 rows)\n<\/pre>\n
If you want to list all the hypothetical indexes you can do this as well:<\/p>\n
\npostgres=# select * from hypopg_list_indexes();\n indexrelid |     indexname     | nspname | relname | amname \n------------+-------------------+---------+---------+--------\n      16399 | btree_t1_a | public  | t1      | btree\n(1 row)\n<\/pre>\n
Of course you can drop them when not anymore required:<\/p>\n
\npostgres=# select * from  hypopg_drop_index(16399);\n hypopg_drop_index \n-------------------\n t\n(1 row)\n\npostgres=# SELECT * FROM hypopg_list_indexes();\n indexrelid | indexname | nspname | relname | amname \n------------+-----------+---------+---------+--------\n(0 rows)\n<\/pre>\n
Hope this helps …<\/p>\n","protected":false},"excerpt":{"rendered":"
It has been quite a while since the last post in this series. Today we’ll look at what you know from Oracle as: Invisible indexes. In case you wonder what they might be useful for: Imagine you want to test if an index would benefit one or more queries without affecting the production workload. In […]<\/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-10335","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nCan I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes - 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\/can-i-do-it-with-postgresql-15-invisible-indexes\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes\" \/>\n<meta property=\"og:description\" content=\"It has been quite a while since the last post in this series. Today we’ll look at what you know from Oracle as: Invisible indexes. In case you wonder what they might be useful for: Imagine you want to test if an index would benefit one or more queries without affecting the production workload. In […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-12T14:26:29+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=\"5 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\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"headline\":\"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes\",\"datePublished\":\"2017-07-12T14:26:29+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/\"},\"wordCount\":453,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/\",\"name\":\"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-07-12T14:26:29+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/4bae5e5dd8c7d48edbd948772b49b202\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/can-i-do-it-with-postgresql-15-invisible-indexes\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes\"}]},{\"@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":"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes - 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\/can-i-do-it-with-postgresql-15-invisible-indexes\/","og_locale":"en_US","og_type":"article","og_title":"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes","og_description":"It has been quite a while since the last post in this series. Today we’ll look at what you know from Oracle as: Invisible indexes. In case you wonder what they might be useful for: Imagine you want to test if an index would benefit one or more queries without affecting the production workload. In […]","og_url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/","og_site_name":"dbi Blog","article_published_time":"2017-07-12T14:26:29+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Open source Team","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"headline":"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes","datePublished":"2017-07-12T14:26:29+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/"},"wordCount":453,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/","url":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/","name":"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-07-12T14:26:29+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/4bae5e5dd8c7d48edbd948772b49b202"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/can-i-do-it-with-postgresql-15-invisible-indexes\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Can I do it with PostgreSQL? \u2013 15 \u2013 invisible indexes"}]},{"@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\/10335","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=10335"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10335\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10335"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10335"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10335"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10335"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}