Tag Info

Hot answers tagged

6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


4

Run ANALYZE after the index has been added. And make sure the column deprovision has statistics. How to verify? Basic statistics in pg_class: SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE oid = 'schema_defs'::regclass; Data histograms per column in pg_stats (pg_statistics): SELECT attname, inherited, n_distinct, ...


4

That may work for your clustered indexes, but you'll also have nonclustered indexes to contend with. They can suffer a lot too, and that's often where it can matter the most. But you should consider the impact. There are times when fragmentation can be a big thing (such as when you delete data from heaps a lot), and times when it's really not such a huge ...


3

This will give you the estimated time remaining select session_id ,command ,wait_time ,CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar (10)) + ' sec' as running_time ,percent_complete ,CAST((estimated_completion_time %60000)/1000 as varchar (10)) + ' sec' as est_time_to_go ...


2

First of all, I have to ask: Why are you doing this? If you have a performance issue, pursue it. However, you only mention that you are seeing scans instead of seeks. Scans are not always bad - they can be the most efficient method of pulling large amounts of data since sequential file access is less costly in I/O terms than random access. The commenters ...


2

You need to analyze the wait chain. What is likely happening is that the OIB has completed the first two phases (perhaps long ago), and is now waiting for user queries to drain in order to do the final phase. One or more user queries is blocking OIB final phase, and as OIB has requested an SCH_M lock it places every other user queries in the wait list. The ...


2

move to SQL Server 2014 that offers updateable clustered columnstore index. if stuck to 2012, use partition switch to update the nonclustered columnstore index.


2

I've just did a test, and apparently it forces the query not to use any index: mysql> explain select email from B2 where length(email)>30; +----+-------------+-------+-------+---------------+-------+---------+------+--------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra ...


2

Index Hoarder is telling you that historically this index is not being used. Therefore, the maintenance of the index is a cost in CPU, I/O, and storage, but you are not getting any performance benefit from those indexes. A general policy of considering Foreign Keys for indexes is good general plan, but an index that is not used should most likely be ...


2

version <= 11.2? No.* But in 12c, there is a new feature called Asynchronous Global Index Maintenance. The DROP PARTITION operation completes immediately, leaving the global index in usable state. The trick is, that index entries are not maintained, they are orphaned, and these orphaned entries will be cleaned up later. The cleanup process happens ...


1

As it states on the ALTER INDEX documentation, using ALTER INDEX ...REBUILD is equivalent to DBCC DBREINDEX. ALTER INDEX has far more functionality in terms of other arguments and DBCC DBREINDEX is deprecated, however the performance issues you face should not be related to using the old method of re-indexing.


1

That is because SQL Server reads entire pages instead of records and when a index is used SQL Server might need to follow the bookmark to fetch the fields that are not included in the index. So if your query isn't selective enough, it might end up reading all the pages of the index, and following the bookmarks to read all the pages of the actual table too. ...


1

I missed that you are already adjusting for maintenance_work_mem in my first read. I'll leave quote and advice in my answer for reference. Per documentation: maintenance_work_mem (integer) Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 ...


1

SUGGESTION #1 Since you are having issues with swap and looking at your current InnoDB settings, I would suggest one of the following settings: innodb_buffer_pool_instances=2 # If your Server is DualCore innodb_buffer_pool_instances=4 # If your Server is QuadCore innodb_buffer_pool_instances=6 # If your Server is HexaCore innodb_buffer_pool_size=90G I ...



Only top voted, non community-wiki answers of a minimum length are eligible