UNPKG

12.6 kBMarkdownView Raw
1# pg-extras
2
3A heroku plugin for awesome pg:* commands that are also great and fun and super.
4
5### Installation
6
7```bash
8$ heroku plugins:install heroku-pg-extras
9```
10
11### Usage
12
13```bash
14$ heroku pg:cache-hit
15 name | ratio
16----------------+------------------------
17 index hit rate | 0.99957765013541945832
18 table hit rate | 1.00
19(2 rows)
20```
21
22```
23$ heroku pg:index-usage
24 relname | percent_of_times_index_used | rows_in_table
25---------------------+-----------------------------+---------------
26 events | 65 | 1217347
27 app_infos | 74 | 314057
28 app_infos_user_info | 0 | 198848
29 user_info | 5 | 94545
30 delayed_jobs | 27 | 0
31(5 rows)
32```
33
34```
35$ heroku pg:ps
36 procpid | source | running_for | waiting | query
37---------+------------------------------------------+-----------------+---------+-----------------------
38 31776 | psql | 00:19:08.017088 | f | <IDLE> in transaction
39 31912 | psql | 00:18:56.12178 | t | select * from hello;
40 32670 | Heroku Postgres Data Clip daaiifuuraiyks | 00:00:25.625609 | f | BEGIN READ ONLY; select pg_sleep(60)
41(2 rows)
42```
43
44```
45$ heroku pg:locks
46 procpid | relname | transactionid | granted | query_snippet | age
47---------+---------+---------------+---------+-----------------------+-----------------
48 31776 | | | t | <IDLE> in transaction | 00:19:29.837898
49 31776 | | 1294 | t | <IDLE> in transaction | 00:19:29.837898
50 31912 | | | t | select * from hello; | 00:19:17.94259
51 3443 | | | t | +| 00:00:00
52 | | | | select +|
53 | | | | pg_stat_activi |
54(4 rows)
55```
56
57```
58$ heroku pg:outliers
59 qry | exec_time | prop_exec_time | ncalls | sync_io_time
60-----------------------------------------+------------------+----------------+-------------+--------------
61 SELECT * FROM archivable_usage_events.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
62 COPY public.archivable_usage_events (.. | 50:38:33.198418 | 23.6% | 13 | 13:34:21.00108
63 COPY public.usage_events (id, reporte.. | 02:32:16.335233 | 1.2% | 13 | 00:34:19.784318
64 INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
65 SELECT * FROM usage_events WHERE (alp.. | 01:18:10.754354 | 0.6% | 102,114,301 | 00:00:00
66 UPDATE usage_events SET reporter_id =.. | 00:52:35.683254 | 0.4% | 23,786,348 | 00:00:00
67 INSERT INTO usage_events (id, retaine.. | 00:49:24.952561 | 0.4% | 21,988,201 | 00:00:00
68 COPY public.app_ownership_events (id,.. | 00:37:14.31082 | 0.3% | 13 | 00:12:32.584754
69 INSERT INTO app_ownership_events (id,.. | 00:26:59.808212 | 0.2% | 383,109 | 00:00:00
70 SELECT * FROM app_ownership_events .. | 00:19:06.021846 | 0.1% | 744,879 | 00:00:00
71(10 rows)
72```
73
74```
75$ heroku pg:calls
76 qry | exec_time | prop_exec_time | ncalls | sync_io_time
77-----------------------------------------+------------------+----------------+-------------+--------------
78 SELECT * FROM usage_events WHERE (alp.. | 01:18:11.073333 | 0.6% | 102,120,780 | 00:00:00
79 BEGIN | 00:00:51.285988 | 0.0% | 47,288,662 | 00:00:00
80 COMMIT | 00:00:52.31724 | 0.0% | 47,288,615 | 00:00:00
81 SELECT * FROM archivable_usage_event.. | 154:39:26.431466 | 72.2% | 34,211,877 | 00:00:00
82 UPDATE usage_events SET reporter_id =.. | 00:52:35.986167 | 0.4% | 23,788,388 | 00:00:00
83 INSERT INTO usage_events (id, retaine.. | 00:49:25.260245 | 0.4% | 21,990,326 | 00:00:00
84 INSERT INTO usage_events (id, retaine.. | 01:42:59.436532 | 0.8% | 12,328,187 | 00:00:00
85 SELECT * FROM app_ownership_events .. | 00:19:06.289521 | 0.1% | 744,976 | 00:00:00
86 INSERT INTO app_ownership_events(id, .. | 00:26:59.885631 | 0.2% | 383,153 | 00:00:00
87 UPDATE app_ownership_events SET app_i.. | 00:01:22.282337 | 0.0% | 359,741 | 00:00:00
88(10 rows)
89```
90
91```
92$ heroku pg:blocking
93 blocked_pid | blocking_statement | blocking_duration | blocking_pid | blocked_statement | blocked_duration
94-------------+--------------------------+-------------------+--------------+------------------------------------------------------------------------------------+------------------
95 461 | select count(*) from app | 00:00:03.838314 | 15682 | UPDATE "app" SET "updated_at" = '2013-03-04 15:07:04.746688' WHERE "id" = 12823149 | 00:00:03.821826
96(1 row)
97```
98
99
100```
101$ heroku pg:pull DATABASE localdbname --app myapp
102```
103```
104$ heroku pg:push localdbname DATABASE --app myapp
105```
106
107```
108$ heroku pg:total-index-size
109 size
110-------
111 28194 MB
112(1 row)
113```
114
115```
116$ heroku pg:index-size
117 name | size
118---------------------------------------------------------------+---------
119 idx_activity_attemptable_and_type_lesson_enrollment | 5196 MB
120 index_enrollment_attemptables_by_attempt_and_last_in_group | 4045 MB
121 index_attempts_on_student_id | 2611 MB
122 enrollment_activity_attemptables_pkey | 2513 MB
123 index_attempts_on_student_id_final_attemptable_type | 2466 MB
124 attempts_pkey | 2466 MB
125 index_attempts_on_response_id | 2404 MB
126 index_attempts_on_enrollment_id | 1957 MB
127 index_enrollment_attemptables_by_enrollment_activity_id | 1789 MB
128 enrollment_activities_pkey | 458 MB
129 index_enrollment_activities_by_lesson_enrollment_and_activity | 402 MB
130 index_placement_attempts_on_response_id | 109 MB
131 index_placement_attempts_on_placement_test_id | 108 MB
132 index_placement_attempts_on_grade_level_id | 97 MB
133 index_lesson_enrollments_on_lesson_id | 93 MB
134(truncated results for brevity)
135```
136
137```
138$ heroku pg:table-size
139 name | size
140---------------------------------------------------------------+---------
141 learning_coaches | 196 MB
142 states | 145 MB
143 grade_levels | 111 MB
144 charities_customers | 73 MB
145 charities | 66 MB
146(truncated results for brevity)
147```
148
149```
150$ heroku pg:table-indexes-size
151 table | indexes_size
152---------------------------------------------------------------+--------------
153 learning_coaches | 153 MB
154 states | 125 MB
155 charities_customers | 93 MB
156 charities | 16 MB
157 grade_levels | 11 MB
158(truncated results for brevity)
159```
160
161```
162$ heroku pg:total-table-size
163 name | size
164---------------------------------------------------------------+---------
165 learning_coaches | 349 MB
166 states | 270 MB
167 charities_customers | 166 MB
168 grade_levels | 122 MB
169 charities | 82 MB
170(truncated results for brevity)
171```
172
173```
174$ heroku pg:unused-indexes
175 table | index | index_size | index_scans
176---------------------+--------------------------------------------+------------+-------------
177 public.grade_levels | index_placement_attempts_on_grade_level_id | 97 MB | 0
178 public.observations | observations_attrs_grade_resources | 33 MB | 0
179 public.messages | user_resource_id_idx | 12 MB | 0
180(3 rows)
181```
182
183```
184$ heroku pg:seq-scans
185
186 name | count
187-----------------------------------+----------
188 learning_coaches | 44820063
189 states | 36794975
190 grade_levels | 13972293
191 charities_customers | 8615277
192 charities | 4316276
193 messages | 3922247
194 contests_customers | 2915972
195 classroom_goals | 2142014
196 contests | 1370267
197 goals | 1112659
198 districts | 158995
199 rollup_reports | 115942
200 customers | 93847
201 schools | 92984
202 classrooms | 92982
203 customer_settings | 91226
204(truncated results for brevity)
205```
206
207```
208$ heroku pg:long-running-queries
209
210 pid | duration | query
211-------+-----------------+---------------------------------------------------------------------------------------
212 19578 | 02:29:11.200129 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1450645 LIMIT 1
213 19465 | 02:26:05.542653 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1889881 LIMIT 1
214 19632 | 02:24:46.962818 | EXPLAIN SELECT "students".* FROM "students" WHERE "students"."id" = 1581884 LIMIT 1
215(truncated results for brevity)
216```
217
218```
219$ heroku pg:records_rank
220 name | estimated_count
221-----------------------------------+-----------------
222 tastypie_apiaccess | 568891
223 notifications_event | 381227
224 core_todo | 178614
225 core_comment | 123969
226 notifications_notification | 102101
227 django_session | 68078
228 (truncated results for brevity)
229```
230
231```
232$ heroku pg:bloat
233
234 type | schemaname | object_name | bloat | waste
235-------+------------+-------------------------------+-------+----------
236 table | public | bloated_table | 1.1 | 98 MB
237 table | public | other_bloated_table | 1.1 | 58 MB
238 index | public | bloated_table::bloated_index | 3.7 | 34 MB
239 table | public | clean_table | 0.2 | 3808 kB
240 table | public | other_clean_table | 0.3 | 1576 kB
241```
242
243```
244$ heroku pg:vacuum-stats
245 schema | table | last_vacuum | last_autovacuum | rowcount | dead_rowcount | autovacuum_threshold | expect_autovacuum
246--------+-----------------------+-------------+------------------+----------------+----------------+----------------------+-------------------
247 public | log_table | | 2013-04-26 17:37 | 18,030 | 0 | 3,656 |
248 public | data_table | | 2013-04-26 13:09 | 79 | 28 | 66 |
249 public | other_table | | 2013-04-26 11:41 | 41 | 47 | 58 |
250 public | queue_table | | 2013-04-26 17:39 | 12 | 8,228 | 52 | yes
251 public | picnic_table | | | 13 | 0 | 53 |
252
253$ heroku pg:mandelbrot
254```
255
256## THIS IS BETA SOFTWARE
257
258Thanks for trying it out. If you find any issues, please notify us at
259support@heroku.com
260