4
It is not possible as the rebuild operation would be blocked due to the actively running query holding a lock on the table, and therefore it wouldn't complete until after the running query completed. Note this is to ensure data consistency, as some of the data pages of the index could contain a mix of data that was processed and data that was not yet ...
4
Will PostgreSQL use parts of a composite index even if all parts aren't listed in the where clauses?
Yes, because that composite index in your example is also known as a covering index. Specifically mentioned in the previously linked documentation here:
...people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing
CREATE INDEX tab_x_y ON tab(x, y);
even though they had no intention of ever using y as ...
3
Other than using query hints to force different indexes, or using Query Store to force plans that don't use the index, no. There's no way to "hide" an enabled index from the query optimizer.
3
When two indexes both satisfy a query, you'll find that the smaller of the two is selected. Often, this means the narrower index, but it also holds true with fully duplicated, identical indexes, with the physically smaller one being preferred.
Unless you're scanning the index and the full size matters, your index tuning Spidey Sense is right. Drop IX_1 and ...
3
Given the fact that the query plan was built before the query kicked off, the answer is no.
2
It is not so common that we get questions about why the planner does such a good job!
By looking at expected rows values given in the Index Scan Backwards and in the Bitmap Heap Scan (or equivalently the Sort) nodes, we see that for one it expects to find 293 rows, and for the other it expects 7. Yes, this difference does come from the stats. Specifically, ...
2
It is easy enough to benchmark this, but the INSERT performance of UUIDs will be worse, because they are bigger and slower to generate.
But it doesn't sound like you are building a high performance application anyway (then you probably wouldn't be using JSON), so it probably won't make much difference.
Finally, you want to use UUIDs for security reasons (...
1
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 ...
1
Up to three times than the size of new index:
index structure itself
WAL records during create index
temporary files while sorting index values
I have deleted old records in this table
It is possible to reclaim space using the pgcompacttable tool. pgcompacttable was designed specifically for compressing tables when there is not enough space for a copy of ...
1
Task 1 may be solved by
SELECT t1.*
FROM test t1
JOIN test t2 USING (b_id, c_id, d_id)
WHERE (t1.a_id, t2.a_id) IN ((1,2), (2,1))
1
A clustered index IS the table - so if the rows you are adding are already ordered there is not much work to do. A non-clustered index is an extra structure that you will have to maintain - so Inserts, Updates & Deletes will all have an overhead, the more indexes the higher the overhead. Generally, there are some selects for every table (otherwise what ...
1
The query in this question was inserting in to a table (Object1) that had a clustered index on a random GUID. I suspect that this may have been the problem since the query would have had to recalculate and reorder the index at each pass as it inserted newly generated results (please correct me if I'm wrong, I want to learn this stuff).
I removed the ...
1
What Laurenz said is true, but I've actually found a more measurable difference in performance when you try to use those UUID fields in predicates (e.g. in JOIN, WHERE, and HAVING clauses). Again, it'll depend on the amount of data between the tables your predicates are for, but a comparison between a 16 byte value and another 16 byte value is somewhat ...
1
please take a look at this.
How to estimate the size of a NCI, you will find all key factors playing role on the final index size.
https://docs.microsoft.com/en-us/sql/relational-databases/databases/estimate-the-size-of-a-nonclustered-index?view=sql-server-ver15
1
If you drop IX_1, all queries that were previously using it will now start using IX_2. With IX_2 being larger (one more key field and two included fields), it has:
More non-leaf level pages to support the second key column
More leaf level pages to store the INCLUDE column values for each row
Any queries that currently use IX_1 most likely don't need these ...
Only top voted, non community-wiki answers of a minimum length are eligible
