Suggested Indexes
Add indexes to speed up queries.
rails generate migration add_suggested_indexes
And paste
commit_db_transaction
add_index :items, "name gist_trgm_ops", using: "gist", algorithm: :concurrently
Details
CREATE INDEX CONCURRENTLY ON items USING gist (name gist_trgm_ops)
Rows: 27485
Row progression: 27485, 2749
Row estimates
- name (~~*): 2749
- is_medicine (=): 13743
- status (=): 13743
- name (sort): 1
Existing indexes
- id PRIMARY
- dispensing_unit_id
- expiry_date_present
- generic_name
- is_asset
- is_consignment
- item_subtype_id
- name
- rc_code
- status
to speed up
| Total Time |
Average Time |
Calls |
|
0 min
0.3%
|
23 ms
|
52
parasapp
·
details
|
SELECT "items".* FROM "items" WHERE ((name) ilike $1 and is_medicine=$2 and status=$3) ORDER BY name LIMIT $4
|
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 |
|
0 min
7%
|
39 ms
|
760
pghero
·
details
|
SELECT n.nspname AS schema, c.relname AS relation, CASE WHEN c.relkind = $1 THEN $2 ELSE $3 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 ($4, $5) AND n.nspname !~ $6 AND c.relkind IN ($7, $8) ORDER BY pg_table_size(c.oid) DESC, 2 ASC /*pghero*/
|
|
0 min
3%
|
101 ms
|
139
parasapp
·
details
|
SELECT "transaction_details".* FROM "transaction_details" WHERE (narration = $1 and cast(reference_2 as int) = $2 and location_id = $3) LIMIT $4
|
|
0 min
2%
|
53 ms
|
135
parasapp
·
details
|
SELECT id,name,rc_code,item_subtype_id FROM "items" WHERE ("items"."is_consignment" = $1) AND (id in(select iwd.item_id from inventory_statuses as iss inner join item_with_details as iwd on iss.item_with_detail_id=iwd.id where iss.quantity > $2 and iss.inventory_head_id=$3 and iss.flag=$4 and (iwd.expiry_date >= $5 or iwd.expiry_date is null)) and is_asset = $6 and is_consignment = $7 and status = $8) ORDER BY name, lower(name)
|