Improving performance of a fetch query with order parameter on a large table

We have a large table (3 million rows) with very poor fetching performance when a sort is applied without any filters.

MyType.fetch({ order: 'descending(myField)', limit: 100, explain: true})
takes 17 seconds.

myField is a float field with very unique values per row.

Upon inspection of the query plan, we see scanning the whole table.

Limit (cost=694508.19…694508.44 rows=101 width=4092)
-> Sort (cost=694508.19…702347.03 rows=3135536 width=4092)
Sort Key: myfield_d DESC, id DESC
-> Seq Scan on c3_2_item_facility t (cost=0.00…574445.20 rows=3135536 width=4092)
Filter: (tenant_tag_id = ‘7’::numeric)

The table is very wide, 122 columns, but perhaps we can split columns out into a few types and use references.

What are some optimizations we can make to speed this up.

Thanks

Does the table already have an index on myField?

c3 show type has the index defined (among some others)
@db(index=['myfield1,myfield2', 'myfield1', 'myfield2', 'myField'])

where as

MyType.schema().tables()[0].indexes

does not show that one, at least that I can find. But it does have 8 other indexes.

Is it possible that it is refusing to make the index for some reason?

It may be. You should look for a database error message in the splunk logs. Did you insert that index recently? Also, try provisioning with a reset flag.

The index has been there for several weeks. Upon inspecting the static console warnings, I see a few messages, but unsure if they are related to this index.

Error creating index for type MyType: Unable to execute sql DROP INDEX C3_2_MY_TYPE_PK
ERROR: cannot drop index c3_2_mytype_pk because constraint c3_2_mytype_pk on table c3_2_mytype requires it
Hint: You can drop constraint c3_2_mytype_pk on table c3_2_mytype instead. 

Along with several others from other files/types.

Also, does it matter that this field is a stored calc?

myField : double stored calc "latestValue(myReference[0].data.value)"

The values in myField are working, so I wouldn’t expect that would cause a problem.

It looks like something went wrong during the index creation process. A database administrator will need to look at this. You should file a support ticket reporting this error.

It should not matter that this is a scored calc but, please, report it too.

@karlbloedorn The error about not being able to drop the index is a bug that is fixed in 7.9. Is this a system I can access? If so, I can probably figure out what is going on.

@karlbloedorn I investigated the errors in:

https://3m-qa-invento-prime.c3iot.ai/static/console/

(url was provided by @brett.thompson)

The error for the ItemFacilityType is due to the fact that someone defined an index of “id” either in that type or in a type that it extends. This would be a totally useless index since there is already a system defined primary key that takes care of that. Due to a bug in 7.8, this causes an error. In 7.9, the additional “useless” index will be created and no error will be thrown. You should track this down, remove that index definition and reprovision. I believe the other types reporting the issue have a similar issue.

I’m not 100% sure if this is specifically the issue you were referring to as you indicate the affected type is “MyType”, which doesn’t exist, at least not in that tag.

Ok, that is an easy fix for that error, but I don’t believe that would fix our performance issue on the sorting., right? Appreciate the help so far.

If the intended indexes were created, it should fix the sort performance, at least for the query I believe you are trying to do. Other queries may have different performance characteristics and would need to be investigated if that is the case.

Yes this has resolved the issue of the index being created and used upon query. I tested at small scale with minimal data, next I will test on the 3 million table tag and see if the behavior improves from before the index.

@trothwein

while this worked, we had to remove from our UI query, a method using a primary and secondary sort that moved N/A values (i.e. NULL or empty or no value), to the bottom of the sort. I found a thread that had another method descending(field,true) in order to do the same thing, however this also prevents use of the index.

Here is a link to the post