5
votes
Can PostgreSQL use indexes to expedite count(distinct) queries?
There is a separate entry in a B-tree index for every row, for duplicates, too. So we can never "simply count the number of rows in the index". There are as many index rows to read and ...
- 152k
4
votes
Accepted
Do missing index DMVs work with indexed views?
The missing index DMVs only allow for a maximum of 600 entries (threshold details), so it is possible some suggestions were aged out.
There are other ways this transient information can be purged, as ...
- 70.8k
4
votes
Composite index with WHERE and GROUP BY
You would normally want either the first index
INDEX(a, b, c)
or a similar one
INDEX(b, a, c)
This is because the a and b columns can then be seeked (reading only the exact necessary rows), and the ...
- 5,643
2
votes
Why is this simple query making up 50% execution time?
With the right indexes (you haven't told us what indexes the other table has) you could get this to be efficient by writing the query this way:
SELECT whatever FROM "auth_user" where "...
- 32k
2
votes
Accepted
How to improve simple = condition on a large table?
Your query filters millions of rows to return a hand full of values. So, yes, adding this index will help massively:
CREATE INDEX teammgr_team_name_idx ON teammgr_team (name);
If your query always ...
- 152k
1
vote
Optimal procedure for partitioning with auto-increment PK as boundary value
Partitioning in SQL Server isn’t as straightforward as it is in other systems, so you should have a bit of a think if it’s really the right option.
“I'm hoping partitioning will allow me to reduce ...
- 2,468
1
vote
Check int4range includes number using B-tree index
Since you have relatively few distinct values, there is an efficient solution with a B-tree index. Probably faster than a query based on the GiST index for more than a few result rows. The GiST ...
- 152k
1
vote
Advantage of using INCLUDE as against adding the column in INDEX for covering index
In addition to Erwin's great answer, there is an additional advantage to using the INCLUDE syntax: documentation.
Imagine that you decide that you need an index on columns (a, b) of table tab. Now you ...
- 33.2k
1
vote
Advantage of using INCLUDE as against adding the column in INDEX for covering index
The difference between covering and including isn't when selecting, it is when inserting and updating. The INCLUDEd columns do not have to be kept in a stable order so if you update those columns (...
- 30.3k
1
vote
Accepted
Advantage of using INCLUDE as against adding the column in INDEX for covering index
Rule of thumb 1: If you never use an index column for filtering or sorting (or joining, or to enforce uniqueness), you might as well move it to the INCLUDE clause. Nothing lost, something gained.
Rule ...
- 152k
1
vote
Composite index with WHERE and GROUP BY
Well, it is difficult to say without having more details on the data and data distribution. In addition to great answer by Charlieface and comment made by Brendan McCaffrey, I would like to say that ...
- 3,645
1
vote
Accepted
What is performance impact of indexing UpdatedAt/ModifiedAt of frequently updated tables?
indexing columns that are frequently updated can be bad for performance
This is too broad of a statement to be meaningful. Indexed columns can certainly be good for performance when querying, ...
- 21.5k
1
vote
Complicated query running orders of magnitude faster than simple query
Try this order:
INDEX(emailed, -- for the `WHERE`
user_id, id) -- for the `ORDER BY
Note: In this context, IS NULL optimizes similar to = constant. So, I put that column first, ...
- 68.7k
1
vote
Agregation group by date or group, pair index, MySQL, slow response
That's one month's worth of data? Are you having any trouble with 20K inserts every 15 minutes? (I would expect not.)
The table can be shrunk:
ad_group_name_id probably does not need to be a 4-byte ...
- 68.7k
Only top scored, non community-wiki answers of a minimum length are eligible