Adding an index is definitely a good idea for this. However, DATEPART(YEAR, datecolumn) = 2012 isnt' sargable so it will still do a scan of the index.
If you want it to use the index then you will need to do:
WHERE dateColumn >= '1/1/2012' AND dateColumn < '1/1/2013'
Please note the placement of the >= and the < signs to get the correct ...
The word you're looking for is "SARGable". From Wikipedia:
In relational databases, a condition (or predicate) in a query is said
to be sargable if the DBMS engine can take advantage of an index to
speed up the execution of the query. The term is derived from a
contraction of Search ARGument ABLE.
I think that's good, because it lowers page contention (vs inserting them all on the last data page all the time), but I'm not sure about that.
At insert rates of under 10,000/sec hot page latch contention is not a big issue, and the efficiency and locality of end-of-index inserts is preferable.
Change the primary key to be composite on (started, id) so it'...
This information can be found in the system tables, sys.spatial_indexes and sys.spatial_index_tessellations. More info for these system tables at https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/spatial-data-catalog-views?view=sql-server-ver15.
Note that you should also create an index with the default operator
class if you want queries involving ordinary <, <=, >, or >=
comparisons to use an index. Such queries cannot use the
xxx_pattern_ops operator classes. (Ordinary equality comparisons can
use these operator classes, however.) It is possible to create
multiple indexes ...
because on partitioned tables on Postgres the partition key must belong always to the index
This is a misconception, and perhaps accidentally the cause of the problem. The partition key must be part of the primary key or part of a parental unique index, but it does not need to be part of other indexes. So you can create a index on (fk_x_orders_id) on the ...
I don't know why the results of a biochemical experiment would be enforced to be unique in the first place, but you can use an exclusion constraint that tests for equality. It will automatically resolve hash collisions by doing the character-by-character comparison.
alter table foo add constraint foo__bar__un exclude using hash (bar with =);
But you should ...
A multicolumn expression index should serve your particular query best:
CREATE INDEX ON public.core_user (upper(email), last_login DESC);
If last_login can be NULL, consider NULLS LAST - in index and query.
The rule of thumb is: equality first, range later. See:
Multicolumn index and performance
Get the last 5 distinct values for each ID
Creating and dropping indexes does not harm the table in the least. Also, with a partial index, you will pay the price for index maintenance only if the row matches the condition.
You should consider range partitioning by updated_at. Then queries with that column in the WHERE condition will only scan the necessary partitions. As a side effect, deleting old ...
So could there be edge cases, or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?
I'm not sure we can be so unequivocal. It's difficult to say "always" or "never" about anything. There may be an exception case we haven't thought of.
Say for example you are in the middle of refactoring ...
Answer compiled from comments by:
No reason for to restrict index creation when the statement is syntactically correct. The most common issue - CREATE TABLE t (id SERIAL PRIMARY KEY); which creates both UNIQUE index (due to SERIAL definition) and PRIMARY KEY (due to explicit specifying). So excess UNIQUE key must be removed by an additional statement.
As with everything related to databases, the answer will be “it depends”.
BRIN indexes are efficient if the ordering of the key values follows the organization of blocks in the storage layer. In the simplest case, this could require the physical ordering of the table, which is often the creation order of the rows within it, to match the order of the key(s). ...
The two queries are not equivalent.
The first query without view runs window functions after applying the WHERE clause.
The second query on the view runs window functions before applying the WHERE clause.
This can lead to different results. And (obviously) to different query plans.
Your second view peniot_json.all_pde_data does not use window functions (or ...