. I’m still working on the same table as in the previous posts, with 10000 rows in 1429 pages. 5% of rows is 500 rows and 5% of blocks is about 72 pages.<\/p>\n Sometimes, you can answer your business question on a sample of rows, when you need an approximate result, trend or pattern Let’s say that you want to sum() on only 5 percent of rows:<\/p>\n This row sampling reads all rows and picks a sample of them at random. Unfortunately, it reads all blocks because you cannot get a good sample if you don’t know how many rows you have in each block. Working on a sample can make sense if you want to apply complex operations on the result. Here the cost in the database is similar to a Seq Scan: 1429 blocks read at seq_page_cost=1, but the sum() applied on 500 rows (cpu_operator_cost=0.0025) and 500 tuples from the scan and 1 tuple for the result, with cpu_tuple_cost=0.01<\/p>\n From execution statistics, you can see that the result is exactly what we asked: 500 rows returned.<\/p>\n Oracle has a different syntax and different algorithm:<\/p>\n Here we have not read all the blocks. Only 40% of them. This is faster than the Postgres approach, but the drawback is that the result is not exact: 478 rows were returned here.<\/p>\n When we can afford an approximate sampling, we can sample on blocks rather than on rows:<\/p>\n The number of rows is still good here, but the result may depend on the blocks sampled. Only 73 blocks were read, which is exactly 5% and of course, the rows may be distributed differently within the blocks. However, the advantage is that it is faster as it reads less blocks. But those blocks being picked at random, they are by definition random reads: 71 pages read at random_page_cost=0:4 and, as in the previous case, 501 cpu_tuple_cost and 500 cpu_operator_cost<\/p>\n With block sampling, Oracle reads a smaller number of blocks than with row sampling, but still more than 5%, and the number of rows is not exact: 798 rows here:<\/p>\n Again, as for the previous access paths: same concepts and different implementation between Postgres and Oracle. Everything looks similar and easily portable from a far overview, but going into details you see all those little differences which make it no so easy to be database agnostic or easily portable.<\/p>\n This is the end of this series comparing Postgres access path with Oracle ones. The goal is not to tell you that one is better than the other. They have a different approach, different targets, different price, different history and probably future. But understanding how they work and how they estimate the cost is a good way to learn. I’m myself learning a lot about Postgres while writing those posts, matching things I discover on Postgres with those I know for a while in Oracle.<\/p>\n Here is the list of posts on Access Path:<\/p>\n I think my next series will be on Join methods.<\/p>\n","protected":false},"excerpt":{"rendered":" By Franck Pachot . I was going to end this series with the previous post because the last access path available in Postgres is a bit special: a Seq Scan that returns only a sample of the rows, at random. However, it is the occasion to come back to the difference between random and sequential […]<\/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-10450","post","type-post","status-publish","format-standard","hentry","category-oracle","category-postgresql","tag-execution-plan","tag-oracle","tag-postgresql"],"acf":[],"yoast_head":"\n
\nI was going to end this series with the previous post<\/a> because the last access path available in Postgres is a bit special: a Seq Scan that returns only a sample of the rows, at random. However, it is the occasion to come back to the difference between random and sequential reads.
\n<\/p>\nRows<\/h3>\n
explain (analyze,verbose,costs,buffers) select sum(a) from demo1 tablesample bernoulli(5) ;\n QUERY PLAN\n----------------------------------------------------------------------------------------------------------------------\n Aggregate (cost=1435.25..1435.26 rows=1 width=8) (actual time=1.940..1.940 rows=1 loops=1)\n Output: sum(a)\n Buffers: shared hit=1429\n -> Sample Scan on public.demo1 (cost=0.00..1434.00 rows=500 width=4) (actual time=0.007..1.890 rows=509 loops=1)\n Output: n, a, x\n Sampling: bernoulli ('5'::real)\n Buffers: shared hit=1429\n Planning time: 0.373 ms\n Execution time: 1.956 ms<\/code><\/pre>\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID 1tsadjdd9ddam, child number 0\n-------------------------------------\nselect \/*+ *\/ sum(a) from demo1 sample(5)\n-----------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |\n-----------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 397 (100)| 1 |00:00:00.01 | 581 |\n| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 581 |\n| 2 | TABLE ACCESS SAMPLE| DEMO1 | 1 | 500 | 397 (0)| 478 |00:00:00.01 | 581 |\n-----------------------------------------------------------------------------------------------------\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 1 - (#keys=0) SUM(\"A\")[22]\n 2 - (rowset=256) \"A\"[NUMBER,22]<\/code><\/pre>\nBlocks<\/h3>\n
explain (analyze,verbose,costs,buffers) select sum(a) from demo1 tablesample system(5) ;\n QUERY PLAN\n---------------------------------------------------------------------------------------------------------------------\n Aggregate (cost=290.25..290.26 rows=1 width=8) (actual time=0.479..0.480 rows=1 loops=1)\n Output: sum(a)\n Buffers: shared hit=73\n -> Sample Scan on public.demo1 (cost=0.00..289.00 rows=500 width=4) (actual time=0.016..0.377 rows=511 loops=1)\n Output: n, a, x\n Sampling: system ('5'::real)\n Buffers: shared hit=73\n Planning time: 0.698 ms\n Execution time: 0.509 ms<\/code><\/pre>\nPLAN_TABLE_OUTPUT\n------------------------------------------------------------------------------------------------------------------------------------------------------\nSQL_ID fqgbwqfavgdrn, child number 0\n-------------------------------------\nselect \/*+ *\/ sum(a) from demo1 sample block(5)\n-----------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |\n-----------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 22 (100)| 1 |00:00:00.01 | 134 |\n| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 134 |\n| 2 | TABLE ACCESS SAMPLE| DEMO1 | 1 | 500 | 22 (0)| 798 |00:00:00.01 | 134 |\n-----------------------------------------------------------------------------------------------------\nColumn Projection Information (identified by operation id):\n-----------------------------------------------------------\n 1 - (#keys=0) SUM(\"A\")[22]\n 2 - (rowset=256) \"A\"[NUMBER,22]\n<\/code><\/pre>\nSummary<\/h3>\n
\n