Suggested Indexes
Add indexes to speed up queries.
rails generate migration add_suggested_indexes
And paste
commit_db_transaction
add_index :pghero_space_stats, [:relation, :captured_at], algorithm: :concurrently
Details
CREATE INDEX CONCURRENTLY ON pghero_space_stats (relation, captured_at)
Rows: 3110263
Row progression: 3110263, 639, 64
Row estimates
- relation (=): 639
- captured_at (>=): 311513
- schema (=): 1555132
- database (=): 3110263
Existing indexes
- id PRIMARY
- database, captured_at
to speed up
| Total Time |
Average Time |
Calls |
|
1 min
15%
|
74 ms
|
577
pghero
·
details
|
SELECT captured_at, size AS size_bytes FROM pghero_space_stats WHERE database = $1 AND captured_at >= $2 AND schema = $3 AND relation = $4 ORDER BY 1 ASC /*pghero*/
|
Slow Queries
Slow queries take 20 ms or more on average and have been called at least 100 times.
Explain queries to see where to add indexes.
| Total Time |
Average Time |
Calls |
|
2 min
33%
|
165 ms
|
584
pghero
·
details
|
SELECT n.nspname AS schema, c.relname AS relation, CASE c.relkind WHEN $1 THEN $2 WHEN $3 then $4 ELSE $5 END AS type, pg_table_size(c.oid) AS size_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname NOT IN ($6, $7) AND n.nspname !~ $8 AND c.relkind IN ($9, $10, $11) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/
|
|
1 min
15%
|
74 ms
|
577
pghero
·
details
|
SELECT captured_at, size AS size_bytes FROM pghero_space_stats WHERE database = $1 AND captured_at >= $2 AND schema = $3 AND relation = $4 ORDER BY 1 ASC /*pghero*/
Details
CREATE INDEX CONCURRENTLY ON pghero_space_stats (relation, captured_at)
Rows: 3110263
Row progression: 3110263, 639, 64
Row estimates
- relation (=): 639
- captured_at (>=): 311513
- schema (=): 1555132
- database (=): 3110263
Existing indexes
- id PRIMARY
- database, captured_at
|