API tools faq
paste
Guest User

Untitled

a guest
May 23rd, 2018
93
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.90 KB | None | 0 0
raw download clone embed print report
  1. The below compares the performance of the the query before recreating the view, and then after recreating the view.
  2.  
  3. Before: 195.0 ms
  4. After: 0.310 ms
  5.  
  6. The last command, the 'diff', shows that the view itself hasn't changed.
  7.  
  8.  
  9. postgres@postgresql-dev:~$ psql --cluster 8.1/matrix-2008-09-03 itm_matrix_prod
  10. Welcome to psql 8.1.11, the PostgreSQL interactive terminal.
  11.  
  12. Type: \copyright for distribution terms
  13. \h for help with SQL commands
  14. \? for help with psql commands
  15. \g or terminate with semicolon to execute query
  16. \q to quit
  17.  
  18. itm_matrix_prod=# \o /tmp/pre_view_recreate.txt
  19. itm_matrix_prod=# \d sq_vw_ast_perm
  20. itm_matrix_prod=# \o
  21. itm_matrix_prod=# EXPLAIN ANALYZE SELECT DISTINCT assetid, userid, granted FROM sq_vw_ast_perm WHERE assetid = '7337' AND permission = 1;
  22. QUERY PLAN
  23. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  24. Unique (cost=8690.55..8690.76 rows=21 width=96) (actual time=194.035..194.039 rows=2 loops=1)
  25. -> Sort (cost=8690.55..8690.60 rows=21 width=96) (actual time=194.033..194.034 rows=2 loops=1)
  26. Sort Key: assetid, userid, "granted"
  27. -> Subquery Scan sq_vw_ast_perm (cost=8689.62..8690.09 rows=21 width=96) (actual time=193.948..193.956 rows=2 loops=1)
  28. -> Unique (cost=8689.62..8689.88 rows=21 width=71) (actual time=193.945..193.951 rows=2 loops=1)
  29. -> Sort (cost=8689.62..8689.67 rows=21 width=71) (actual time=193.944..193.946 rows=2 loops=1)
  30. Sort Key: assetid, userid, permission, "granted"
  31. -> Append (cost=8236.91..8689.16 rows=21 width=71) (actual time=193.910..193.916 rows=2 loops=1)
  32. -> Hash Join (cost=8236.91..8683.17 rows=19 width=71) (actual time=193.888..193.888 rows=0 loops=1)
  33. Hash Cond: ((("outer".userid)::text = ("inner".roleid)::text) AND (("outer".assetid)::text = ("inner".assetid)::text))
  34. -> Unique (cost=3663.88..3996.33 rows=5681 width=22) (actual time=193.726..193.726 rows=1 loops=1)
  35. -> Sort (cost=3663.88..3730.37 rows=26596 width=22) (actual time=193.723..193.723 rows=1 loops=1)
  36. Sort Key: sq_ast_perm.userid, sq_ast_perm.permission, sq_ast_perm."granted", sq_ast_perm.assetid
  37. -> Seq Scan on sq_ast_perm (cost=0.00..1342.20 rows=26596 width=22) (actual time=0.010..33.566 rows=27239 loops=1)
  38. Filter: (permission = 1)
  39. -> Hash (cost=4572.35..4572.35 rows=136 width=96) (actual time=0.048..0.048 rows=0 loops=1)
  40. -> Unique (cost=4569.63..4570.99 rows=136 width=96) (actual time=0.046..0.046 rows=0 loops=1)
  41. -> Sort (cost=4569.63..4569.97 rows=136 width=96) (actual time=0.045..0.045 rows=0 loops=1)
  42. Sort Key: sq_vw_ast_role.assetid, sq_vw_ast_role.userid, sq_vw_ast_role.roleid
  43. -> Subquery Scan sq_vw_ast_role (cost=4562.09..4564.81 rows=136 width=96) (actual time=0.039..0.039 rows=0 loops=1)
  44. -> Unique (cost=4562.09..4563.45 rows=136 width=309) (actual time=0.037..0.037 rows=0 loops=1)
  45. -> Sort (cost=4562.09..4562.43 rows=136 width=309) (actual time=0.035..0.035 rows=0 loops=1)
  46. Sort Key: assetid, roleid, userid
  47. -> Append (cost=1410.52..4557.27 rows=136 width=309) (actual time=0.024..0.024 rows=0 loops=1)
  48. -> Hash Join (cost=1410.52..4552.19 rows=135 width=196) (actual time=0.020..0.020 rows=0 loops=1)
  49. Hash Cond: (("outer"."?column2?")::text = ("inner".roleid)::text)
  50. -> Append (cost=1406.23..4140.96 rows=27039 width=317) (never executed)
  51. -> Hash Join (cost=1406.23..3858.97 rows=26879 width=190) (never executed)
  52. Hash Cond: (("outer".minorid)::text = ("inner".assetid)::text)
  53. -> Seq Scan on sq_ast_lnk l (cost=0.00..827.79 rows=26879 width=45) (never executed)
  54. -> Hash (cost=1047.38..1047.38 rows=14338 width=145) (never executed)
  55. -> Seq Scan on sq_ast a (cost=0.00..1047.38 rows=14338 width=145) (never executed)
  56. -> Subquery Scan "*SELECT* 2" (cost=0.00..13.20 rows=160 width=317) (never executed)
  57. -> Seq Scan on sq_shdw_ast_lnk s (cost=0.00..11.60 rows=160 width=317) (never executed)
  58. -> Hash (cost=4.29..4.29 rows=1 width=164) (actual time=0.009..0.009 rows=0 loops=1)
  59. -> Index Scan using ast_role_pk on sq_ast_role r (cost=0.00..4.29 rows=1 width=164) (actual time=0.007..0.007 rows=0 loops=1)
  60. Index Cond: (((assetid)::text = '7337'::text) AND ((userid)::text = '0'::text))
  61. -> Index Scan using ast_role_pk on sq_ast_role (cost=0.00..3.72 rows=1 width=309) (actual time=0.001..0.001 rows=0 loops=1)
  62. Index Cond: ((assetid)::text = '7337'::text)
  63. Filter: ((userid)::text <> '0'::text)
  64. -> Index Scan using ast_perm_pk on sq_ast_perm p (cost=0.00..5.78 rows=2 width=22) (actual time=0.020..0.023 rows=2 loops=1)
  65. Index Cond: (((assetid)::text = '7337'::text) AND (permission = 1))
  66. Total runtime: 195.207 ms
  67. (43 rows)
  68.  
  69. itm_matrix_prod=# CREATE OR REPLACE VIEW sq_vw_ast_perm AS
  70. itm_matrix_prod-# SELECT
  71. itm_matrix_prod-# r.assetid, r.userid, p.permission, p.granted
  72. itm_matrix_prod-# FROM
  73. itm_matrix_prod-# (
  74. itm_matrix_prod(# SELECT DISTINCT
  75. itm_matrix_prod(# assetid, userid, roleid
  76. itm_matrix_prod(# FROM
  77. itm_matrix_prod(# sq_vw_ast_role
  78. itm_matrix_prod(# ) r
  79. itm_matrix_prod-# JOIN
  80. itm_matrix_prod-# (
  81. itm_matrix_prod(# SELECT DISTINCT
  82. itm_matrix_prod(# userid, permission, granted, assetid
  83. itm_matrix_prod(# FROM
  84. itm_matrix_prod(# sq_ast_perm
  85. itm_matrix_prod(# ) p
  86. itm_matrix_prod-# ON r.roleid = p.userid
  87. itm_matrix_prod-# AND r.assetid = p.assetid
  88. itm_matrix_prod-# UNION
  89. itm_matrix_prod-# SELECT
  90. itm_matrix_prod-# p.assetid, p.userid, p.permission, p.granted
  91. itm_matrix_prod-# FROM
  92. itm_matrix_prod-# sq_ast_perm p;
  93. CREATE VIEW
  94. itm_matrix_prod=# \o /tmp/post_view_recreate.txt
  95. itm_matrix_prod=# \d sq_vw_ast_perm
  96. itm_matrix_prod=# \o
  97. itm_matrix_prod=# EXPLAIN ANALYZE SELECT DISTINCT assetid, userid, granted FROM sq_vw_ast_perm WHERE assetid = '7337' AND permission = 1;
  98. QUERY PLAN
  99. -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  100. Unique (cost=4584.76..4584.79 rows=3 width=96) (actual time=0.138..0.143 rows=2 loops=1)
  101. -> Sort (cost=4584.76..4584.77 rows=3 width=96) (actual time=0.136..0.137 rows=2 loops=1)
  102. Sort Key: assetid, userid, "granted"
  103. -> Subquery Scan sq_vw_ast_perm (cost=4584.67..4584.74 rows=3 width=96) (actual time=0.110..0.117 rows=2 loops=1)
  104. -> Unique (cost=4584.67..4584.71 rows=3 width=71) (actual time=0.109..0.114 rows=2 loops=1)
  105. -> Sort (cost=4584.67..4584.68 rows=3 width=71) (actual time=0.108..0.109 rows=2 loops=1)
  106. Sort Key: assetid, userid, permission, "granted"
  107. -> Append (cost=4578.48..4584.65 rows=3 width=71) (actual time=0.093..0.097 rows=2 loops=1)
  108. -> Hash Join (cost=4578.48..4578.84 rows=1 width=71) (actual time=0.087..0.087 rows=0 loops=1)
  109. Hash Cond: (("outer".userid)::text = ("inner".roleid)::text)
  110. -> Unique (cost=5.79..5.81 rows=1 width=22) (actual time=0.039..0.039 rows=1 loops=1)
  111. -> Sort (cost=5.79..5.79 rows=2 width=22) (actual time=0.038..0.038 rows=1 loops=1)
  112. Sort Key: sq_ast_perm.userid, sq_ast_perm.permission, sq_ast_perm."granted", sq_ast_perm.assetid
  113. -> Index Scan using ast_perm_pk on sq_ast_perm (cost=0.00..5.78 rows=2 width=22) (actual time=0.017..0.022 rows=2 loops=1)
  114. Index Cond: (('7337'::text = (assetid)::text) AND (permission = 1))
  115. -> Hash (cost=4572.35..4572.35 rows=136 width=96) (actual time=0.033..0.033 rows=0 loops=1)
  116. -> Unique (cost=4569.63..4570.99 rows=136 width=96) (actual time=0.032..0.032 rows=0 loops=1)
  117. -> Sort (cost=4569.63..4569.97 rows=136 width=96) (actual time=0.031..0.031 rows=0 loops=1)
  118. Sort Key: sq_vw_ast_role.assetid, sq_vw_ast_role.userid, sq_vw_ast_role.roleid
  119. -> Subquery Scan sq_vw_ast_role (cost=4562.09..4564.81 rows=136 width=96) (actual time=0.024..0.024 rows=0 loops=1)
  120. -> Unique (cost=4562.09..4563.45 rows=136 width=309) (actual time=0.023..0.023 rows=0 loops=1)
  121. -> Sort (cost=4562.09..4562.43 rows=136 width=309) (actual time=0.021..0.021 rows=0 loops=1)
  122. Sort Key: assetid, roleid, userid
  123. -> Append (cost=1410.52..4557.27 rows=136 width=309) (actual time=0.018..0.018 rows=0 loops=1)
  124. -> Hash Join (cost=1410.52..4552.19 rows=135 width=196) (actual time=0.015..0.015 rows=0 loops=1)
  125. Hash Cond: (("outer"."?column2?")::text = ("inner".roleid)::text)
  126. -> Append (cost=1406.23..4140.96 rows=27039 width=317) (never executed)
  127. -> Hash Join (cost=1406.23..3858.97 rows=26879 width=190) (never executed)
  128. Hash Cond: (("outer".minorid)::text = ("inner".assetid)::text)
  129. -> Seq Scan on sq_ast_lnk l (cost=0.00..827.79 rows=26879 width=45) (never executed)
  130. -> Hash (cost=1047.38..1047.38 rows=14338 width=145) (never executed)
  131. -> Seq Scan on sq_ast a (cost=0.00..1047.38 rows=14338 width=145) (never executed)
  132. -> Subquery Scan "*SELECT* 2" (cost=0.00..13.20 rows=160 width=317) (never executed)
  133. -> Seq Scan on sq_shdw_ast_lnk s (cost=0.00..11.60 rows=160 width=317) (never executed)
  134. -> Hash (cost=4.29..4.29 rows=1 width=164) (actual time=0.003..0.003 rows=0 loops=1)
  135. -> Index Scan using ast_role_pk on sq_ast_role r (cost=0.00..4.29 rows=1 width=164) (actual time=0.003..0.003 rows=0 loops=1)
  136. Index Cond: (((assetid)::text = '7337'::text) AND ((userid)::text = '0'::text))
  137. -> Index Scan using ast_role_pk on sq_ast_role (cost=0.00..3.72 rows=1 width=309) (actual time=0.001..0.001 rows=0 loops=1)
  138. Index Cond: ((assetid)::text = '7337'::text)
  139. Filter: ((userid)::text <> '0'::text)
  140. -> Index Scan using ast_perm_pk on sq_ast_perm p (cost=0.00..5.78 rows=2 width=22) (actual time=0.004..0.006 rows=2 loops=1)
  141. Index Cond: (((assetid)::text = '7337'::text) AND (permission = 1))
  142. Total runtime: 0.310 ms
  143. (43 rows)
  144.  
  145. itm_matrix_prod=#
  146.  
  147. itm_matrix_prod=# \q
  148. postgres@postgresql-dev:~$ diff -u /tmp/pre_view_recrecreate.txt /tmp/post_view_recrecreate.txt
  149. postgres@postgresql-dev:~$
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!