Indexes on d_mcmX

[elavy-harris]

I'm using Mirth 4.4.1 with a Postgres database.

I feel like I've been able to run SQL queries against custom metadata fields with a fast response in the past, e.g.
SELECT * FROM d_mcm9 WHERE "PATIENT_ID" = '123'

Today, this query does a table scan (i.e. prohibitively slow) and I don't see any indexes in d_mcm9.

Did something really change to remove the indexes? Is there any reason for me not to add them manually? Should Mirth be creating them?

[pacmano1]

Custom metadata is not indexed, but you can add your own index on the table.

[elavy-harris]

Thank you @pacmano1 . I can add the index, but as I look at the system further, I'm more confused. If I constrain a single custom metadata field (in this case, "VISIT_ID") for a search and examine the running query in pgAdmin, sometimes I see

SELECT MESSAGE_ID AS MESSAGE_ID, METADATA_ID AS METADATA_ID
		FROM D_MCM9
		 WHERE MESSAGE_ID  >=  $1
				AND MESSAGE_ID  <=  $2
				AND "VISIT_ID" = $3

and sometimes I see

SELECT MESSAGE_ID AS MESSAGE_ID, ID AS METADATA_ID,
				FALSE
		AS TEXT_FOUND
		FROM D_MM9
		 WHERE MESSAGE_ID  >=  $1
				AND MESSAGE_ID  <=  $2

and sometimes

SELECT ID AS MESSAGE_ID
		FROM D_M9 M
		 WHERE ID  >=  $1
				AND ID  <=  $2

(all reformatted for readability).

Performance varies significantly, perhaps depending on buffering. The first query timed out after about 10 minutes, the second completed in about 2.5, and the third finished in about 1. (I've got about 28M messages in the channel right now.)

Creating the index isn't going to do me any good if Mirth isn't going to query that table.

Any guidance?

[pacmano1]

You sure you are not seeing other queries running at the same time? Also, I don't know a world of 28M (million) messages. I would archive / purge long before that count.