Hot answers tagged

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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 "...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 (...
user avatar
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 ...
user avatar
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 ...
user avatar
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, ...
user avatar
  • 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, ...
user avatar
  • 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 ...
user avatar
  • 68.7k

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