Tag Info

Hot answers tagged

7

The clustered index is partitioned on ReadTime so it couldn't use the PK as you describe. It would need to find the Max(Id) for each partition and then find the max of those. It is possible to rewrite the query to get such a plan however. Using an example based on the article here a possible rewrite might be SELECT MAX(ID) AS ID FROM sys.partitions AS P ...


4

This is a known issue regarding Postgres optimization. If the distinct values are few - like in your case - and you are in 8.4+ version, a very fast workaround using a recursive query is described here: Loose Indexscan. Your query could be rewritten (the LATERAL needs 9.3+ version): WITH RECURSIVE pa AS ( ( SELECT labelDate FROM pages ORDER BY labelDate ...


3

By suggestion of @RolandoMySQLDBA, I'm combining my comments into an answer. So, in many cases, LIMIT clause enables the database server to do less work before delivering the result. MySQL docs explain such situations. It is best to check queries with EXPLAIN to see the execution plan. If the execution plan is different with and without limit clause, then ...


3

You are touching on an almost philosophical argument here: should NULLable values be allowed at all given they violate the "closed world" assumption of the model relational databases are derived from (see relevant sections of http://en.wikipedia.org/wiki/Null_(SQL) and a number of other questions here such as Why shouldn't we allow NULLs? for more notes ...


3

I tried creating a small sample of your database, we should probably force the planner to use a semi-join nested loop which would stop processing further rows as soon as it finds the first row for a given hour, camera_id as zugguy suggested. You can have a workaround with the temporary table using generate_series to get hours for the given datetimes. ...


3

Just an idea: create a table with all hours in the day (0-23). create table hours( hr integer ); Then find all hours that have snapshots for the given camera_id and date (of course, you will need to substitute your own camera_id and date into the query): select h.hr, 1 as camera_id from hours h where exists ( select 1 from snapshots s ...


2

The query has a lot of bloat: Two group by, the second is completely unnecessary. Three levels of nesting, the last is also completely unnecessary. It uses GROUP_CONCAT() to find all the product_id that have rows with 'southern' and rows with 'proper'. Not the best way in my opinion. The 1189 AS query_id seems to return redundant information (the same ...


2

processing_date is not in the index, so it cannot use an index only scan. Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.


2

The declaration of singleton in the path expression of the index enforces that you can not add multiple <Number> elements but the XQuery compiler does not take that into consideration when interpreting the expression in the value() function. You have to specify [1] to make SQL Server happy. Using typed XML with a schema does not help with that either. ...


2

You can use a regular expression to match '1 ' to '6 ' at the beginning of the string: data.pro rlike '^([1-6] )' Now put this in a CASE: case when data.pro rlike '^([1-6] )' then cast(substring(data.pro from 1 for 1) as unsigned) end


2

For future readers, I went ahead and tested both methods (with and without the subquery), and they both worked equally well on all tested environments. I do not know if the database statistics needed updated on the databases that originally had a problem or not, but like many developers, I am not in charge of that and don't have any ability to alter it. ...


2

The Maintenance Plan task can reorganize indexes and that should have some benefit for you. Likely, however, you will reorganize more than necessary using that approach. You should look at Ola Hallengren's solution for Maintenance at: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html


2

I assume you are talking about the same query, executing with different indexes. You can't just arbitrarily compare 2 totally different queries that do 2 different things. As Aaron stated in the comments, I can compare my 2001 VW to a 2015 Camaro and there will be no correlation, the Camaro may go faster but that's because it has a bigger engine. ...


1

This is an old question, but I came across this and was appalled at one particular comment made, suggesting the original asked was "confused". The question is perfectly clear, just not strictly about database administration. It falls into the fields of server virtualization and storage provisioning in virtual environments more than anything. It may well be ...


1

Can the Reorganize Index task (maint' plan or ssis package) be used to 'performance tune? Performance tuning is an art. You should avoid knee jerk performance tuning methods - instead you should try to narrow down your investigation. Is this an adequate step of triage for degraded application/query performance? It may or may not be, depending on ...


1

John, I've got a recommendation which may help significantly (I hope!) It requires only a small change to your query, and may yield a significant performance increase. No promises though, but test it and let me know. Apply unnest() Generally speaking, I would avoid using the ANY (array[]) syntax as Craig Ringer metioned, due to the planners limited ...


1

To see if the hardware is not limiting: top/htop => cpu percentage iostat -x 1 => sysstat tool to see disk r/w limits (%util) Concerning locking: Mongo 2.6 : database locking Mongo 3.0 + MMAPv1 storage engine : collection locking Mongo 3.0 + WiredTiger storage engine : document locking If you have 1 huge collection (server-prod), maybe Sharding is an ...


1

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things: ANALYZE TABLE bets; ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */ add a FORCE INDEX to your SELECT


1

A variation on @Akash's answer which uses the LATERAL syntax and results in a better execution plan (check the Index Only Scan using idx2_snapshots on snapshots in the plan below): SELECT hour AS start_hour, hour + interval '1 hour' AS end_hour FROM generate_series('2015-01-01'::timestamp, '2015-01-02 ...


1

Whether a separate table for images is helpful depends on the cardinality of the relationship between the images and the rest of the attributes in the original table: A separate image-table is justifiable if either: multiple rows may contain the same image: enhances normalization since the image only has to be stored once in the image table instead of ...


1

It depends. INDEX(a) SELECT ... ORDER BY a LIMIT 10 -- will, if there is no `WHERE`, stop after 10 rows are read INDEX(b,a) SELECT ... WHERE b=3 ORDER BY a LIMIT 10 -- is likely to use the index for both the WHERE and the ORDER BY, and stop after 10 INDEX(b,a) SELECT ... WHERE b=3 AND c=4 ORDER BY a LIMIT 10 -- This _may_ use the index, but _may_ have to ...


1

Let's walk through the steps. This will make some of the suggestions so far not significant. Scan 2.6 million rows out of 4-5M. Please verify by finding out how many rows have store_id = 1. Scan 2.6 million rows out of 4-5M again. (Yes, a single pass with an OR would be better since there is no way to make use of INDEX(store_id, data_index), which is ...


1

First thing I would do is to combine the inner UNION ALL queries to a single query with the two 'like' conditions 'OR'd together and bracketed away from the other filter. This means it would only pass over that table once instead of twice. You can't add an index to help the 'like' filter as you have a '%' at the beginning. Sorry for brevity of reply, on my ...



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