sys.storage sorted attribute changes for primary key
Hello,
I have a large table (~700 million rows, ~150 columns). Initially after import, the database is very fast on primary key lookups, however after a few thousand creates/updates/deletes on the database, "sorted" (as reported by sys.storage), flips from true to false. This results in major performance degradation.
What are the conditions under which a database can lose it's sorted-ness on the primary key column?
eg:
sql>select schema, table, column, sorted from sys.storage where schema = 'test1' and table = 'cmdb' and column = 'pk_id'; +---------+-------+--------+--------+ | schema | table | column | sorted | +=========+=======+========+========+ | test1 | cmdb | pk_id | false | +---------+-------+--------+--------+
Thanks, -Jeremy Norris
On 3 Feb 2019, at 01:46, Jeremy Norris jnorris@pattern73.com wrote:
Hello,
I have a large table (~700 million rows, ~150 columns). Initially after import, the database is very fast on primary key lookups, however after a few thousand creates/updates/deletes on the database, "sorted" (as reported by sys.storage), flips from true to false. This results in major performance degradation.
What are the conditions under which a database can lose it's sorted-ness on the primary key column?
Hai Jeremy,
Isn’t this because a primary key in the inserted tuples breaks the sortedness of the column? Assuming your updates don’t change the primary keys.
Jennie
eg:
sql>select schema, table, column, sorted from sys.storage where schema = 'test1' and table = 'cmdb' and column = 'pk_id'; +---------+-------+--------+--------+ | schema | table | column | sorted | +=========+=======+========+========+ | test1 | cmdb | pk_id | false | +---------+-------+--------+--------+
Thanks, -Jeremy Norris _______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list
participants (2)
-
Jeremy Norris -
Ying Zhang