{"id":10359,"date":"2017-08-01T05:00:02","date_gmt":"2017-08-01T03:00:02","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/"},"modified":"2017-08-01T05:00:02","modified_gmt":"2017-08-01T03:00:02","slug":"postgres-vs-oracle-access-paths-0","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/","title":{"rendered":"Postgres vs. Oracle access paths – intro"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThis is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have a long experience of reading Oracle execution plans and no experience at all on Postgres. This is my way to learn and share what I learn. You will probably be interested if you are in the same situation: an Oracle DBA wanting to learn about Postgres. But you may also be an experienced Postgres DBA who wants to see a different point of view from a different ‘culture’.
\n
\nI’ll probably use the Oracle terms more often as I’m more familiar with them: blocks for pages, optimizer for query planner, rows for tuples, tables for relations…<\/p>\n

Please, don’t hesitate to comment on the blog posts or through twitter (@FranckPachot<\/a>) if you find some mistakes in my Postgres interpretation. I tend to verify any assumption in the same way I do it with Oracle: the documented behavior and the test result should match. My test should be fully reproducible (using Postgres 9.6.2 here with all defaults). But as I said above, I’ve not the same experience as I have on Oracle when interpreting execution statistics.<\/p>\n

Postgres <\/h3>\n

I’m using the latest versions here. Postgres 9.6.2 (as the one I installed here<\/a>)
\nI’ve installed pg_hint_plan to be able to control the execution plan with hints. This is mandatory when doing some research. In order to understand an optimizer (query planner) choice, we need to see the estimated cost for different possibilities. Most of my tests will be done with: EXPLAIN (ANALYZE,VERBOSE,COSTS,BUFFERS)<\/p>\n

\nfpa=# explain (analyze,verbose,costs,buffers) select 1;\n \n                                     QUERY PLAN\n------------------------------------------------------------------------------------\n Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)\n   Output: 1\n Planning time: 0.060 ms\n Execution time: 0.036 ms\n(4 rows)\n<\/code><\/pre>\n
I my go further with unix tools (like strace to see the system calls)<\/p>\n
Oracle<\/h3>\n
I’m using Oracle 12.2 here and the tests are done by running the statement after setting ALTER SESSION SET STATISTICS_LEVEL=ALL and displaying the execution plan with DBMS_XPLAN:<\/p>\n
select * from dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection');<\/code><\/pre>\n
Note that if you are in lower Oracle versions, you need to call dbms_xplan through the table() function:<\/p>\n
select * from table(dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection'));<\/code><\/pre>\n
Example:<\/p>\n
\nSQL> set arraysize 5000 linesize 150 trimspool on pagesize 1000 feedback off termout off\nSQL> alter session set statistics_level=all;\nSQL> select 1 from dual;\nSQL> set termout on\nSQL> select * from dbms_xplan.display_cursor(format=>'+cost allstats last -plan_hash +projection');\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID  520mkxqpf15q8, child number 0\n-------------------------------------\nselect 1 from dual\n--------------------------------------------------------------------------------------\n| Id  | Operation        | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   |\n--------------------------------------------------------------------------------------\n|   0 | SELECT STATEMENT |      |      1 |        |     2 (100)|      1 |00:00:00.01 |\n|   1 |  FAST DUAL       |      |      1 |      1 |     2   (0)|      1 |00:00:00.01 |\n--------------------------------------------------------------------------------------\n<\/code><\/pre>\n
I’ll probably never compare the execution time, as this depends on the system and makes no sense on artificial small examples. But I’ll try to compare all other statistics: estimated cost, the actual number of pages\/blocks read, etc.<\/p>\n
Table of content<\/h3>\n
Here are the links to the posts:<\/p>\n
    \n
  1. Postgres vs. Oracle access paths \u2013 intro<\/a><\/li>\n
  2. Postgres vs. Oracle access paths I \u2013 Seq Scan<\/a><\/li>\n
  3. Postgres vs. Oracle access paths II \u2013 Index Only Scan<\/a><\/li>\n
  4. Postgres vs. Oracle access paths III – Partial Index<\/a><\/li>\n
  5. Postgres vs. Oracle access paths IV \u2013 Order By and Index<\/a><\/li>\n
  6. Postgres vs. Oracle access paths V \u2013 FIRST ROWS and MIN\/MAX<\/a><\/li>\n
  7. Postgres vs. Oracle access paths VI \u2013 Index Scan<\/a><\/li>\n
  8. Postgres vs. Oracle access paths VII \u2013 Bitmap Index Scan<\/a><\/li>\n
  9. Postgres vs. Oracle access paths VIII \u2013 Index Scan and Filter<\/a><\/li>\n
  10. Postgres vs. Oracle access paths IX \u2013 Tid Scan<\/a><\/li>\n
  11. Postgres vs. Oracle access paths X \u2013 Update<\/a><\/li>\n
  12. Postgres vs. Oracle access paths XI \u2013 Sample Scan<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have […]<\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[59,83],"tags":[348,96,77],"type_dbi":[],"class_list":["post-10359","post","type-post","status-publish","format-standard","hentry","category-oracle","category-postgresql","tag-execution-plan","tag-oracle","tag-postgresql"],"acf":[],"yoast_head":"\nPostgres vs. Oracle access paths - intro - 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\/postgres-vs-oracle-access-paths-0\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Postgres vs. Oracle access paths - intro\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2017-08-01T03:00:02+00:00\" \/>\n<meta name=\"author\" content=\"Oracle 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=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 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\\\/postgres-vs-oracle-access-paths-0\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Postgres vs. Oracle access paths – intro\",\"datePublished\":\"2017-08-01T03:00:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/\"},\"wordCount\":526,\"commentCount\":0,\"keywords\":[\"Execution plan\",\"Oracle\",\"PostgreSQL\"],\"articleSection\":[\"Oracle\",\"PostgreSQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/\",\"name\":\"Postgres vs. Oracle access paths - intro - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2017-08-01T03:00:02+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgres-vs-oracle-access-paths-0\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Postgres vs. Oracle access paths – intro\"}]},{\"@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\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Postgres vs. Oracle access paths - intro - 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\/postgres-vs-oracle-access-paths-0\/","og_locale":"en_US","og_type":"article","og_title":"Postgres vs. Oracle access paths - intro","og_description":"By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/","og_site_name":"dbi Blog","article_published_time":"2017-08-01T03:00:02+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Postgres vs. Oracle access paths – intro","datePublished":"2017-08-01T03:00:02+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/"},"wordCount":526,"commentCount":0,"keywords":["Execution plan","Oracle","PostgreSQL"],"articleSection":["Oracle","PostgreSQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/","url":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/","name":"Postgres vs. Oracle access paths - intro - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2017-08-01T03:00:02+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgres-vs-oracle-access-paths-0\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Postgres vs. Oracle access paths – intro"}]},{"@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\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10359","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\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=10359"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/10359\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=10359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=10359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=10359"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=10359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}