Add option to index custom metadata columns, and allow message browser search to use that index

[rbeckman-nextgen]

Whether the message browser search actually uses the index is dependent on the search criteria. Likely it would only use the index for simple searches where the only criterion is the metadata column itself.

Imported Issue. Original Details:
Jira Issue Key: MIRTH-4445
Reporter: narupley
Created: 2019-07-15T07:52:06.000-0700

[tonygermano]

@cturczynskyj @pladesma please reopen this one

[twest-mirthconnect]

@tonygermano - Will you please provide more justification for this request?

[anibal2j]

Jon Bartels asked me to add a comment to this issue here. I always thought (back from the time I took the Fundamentals course with NextGen) that custom meta data columns were indexed. I've been using custom meta data columns for ever, and I always noticed that they were slow to search. EVERY SINGLE CHANNEL that I have has two custom meta data columns:

MRN
Acc#

And this is required in my case because most of the times I get questions like:

. When did we receive the order for acc#?
. When was the last time we received an order for patient MRN #?
. How many ORMs did I get for acc# ?

I get these requests on a daily basis, multiple times per day. Right now, some of my channels have a lot of data (and I don't want to purge them - I have them set to 90 days which is enough to do my troubleshooting). Doing a search with either acc# or MRN it takes about 10", sometimes a bit more, sometimes less. It'd be great if these columns were automatically indexed so that my search would be super quick.

I'm considering adding manual indexes to the tables like this:

CREATE INDEX CONCURRENTLY metadata_idx ON d_mcm##(your, meta,data,columns)

[pacmano1]

I would suggest the creation of the index be optional.

[jonbartels]

Anibal inspired me to bang out a query which generates the index statements. READ THE COMMENT AND DONT JUST BLINDLY INDEX EVERYTHING. https://gist.github.com/jonbartels/38ffbb101ea32f981cc9950a21ec6809

[anibal2j]

Just to provide an update on this issue. After A LOT of testing and trying things - with huge help from @pacmano1 , @siddharth, @hobbs, @jonbartels we found that just adding an index doesn't provide much help. In fact, we see that the native query in pg is extremely fast. In a table with 1.8m messages, a search by acc# indexed is sub-second. However, when you do the same in MC you're waiting 20 seconds (!!!!).

What we did notice is that if we change the page size in MC's GUI to 1 then the search results come in sub-second! So, there's something here going on in MC's search that it's not showing the data to the user in a timely fashion. This might be a completely different issue that then OP in this issue, but I'll leave that up to you.

For now, my workaround is the following:

1. Create index on acc# custom meta column
2. Change page size to 1 before doing my search

To create the index for channel called "To FFI" for column "ACC_NUMBER" I do the following:

mirth_prod=> select local_channel_id, channel_id, name from d_channels, channel where d_channels.channel_id = channel.id and channel.name = 'To FFI'; local_channel_id | channel_id | name ------------------+--------------------------------------+-------- 68 | 937163d9-e0a0-4e0c-9cb4-db101744e164 | To FFI (1 row)

And then:

CREATE INDEX CONCURRENTLY metadata_idx ON d_mcm68(“MRN”,”ACC_NUMBER”);

[ChristopherSchultz]

I think you want your index to contain message_id as well. Otherwise, it's just an INDEX on the column which still has to go to the clustered-index (or however the table is organized) for the table to get the message_id to then ... get the message.

So you may as well include message_id as a part of the INDEX as well so that the index itself can answer any question you may have for it.

You aren't going to do a SELECT ACCNO FROM d_mcm123 WHERE ACCNO='456' just to see if it's there. No, you are going to ask for the messages which have that matching ACCNO value.