PostgreSQL integer index

Question

PostgreSQL

What is

In och. on a large PostgreSQL plate, we create a field of type integer + btree an index on this field.
Most of the values ​​in the field are default.

What do you need

Select rows sorted by the given field.

Questions

• Which default value will be faster than NULL or 0 ?
• Same for character(n), NULL or ''?

Answer 1

I think you will not feel the difference in speed between NULL and some value. And by the way, you can easily check this, the test for this is simple.

Answer 2

Also asked this question.
Generate some data and check if the index uses the query with WHERE IS NULL (I think 80% NULL will be fine)
If the plan shows the use of an index, then the index stores references to ALL NULL values, and, accordingly, there is no difference.
It would be interesting to create sometimes compact indexes on "sparse" values, where references to NULL values ​​are not stored, but usually in this case they make a one-to-one relationship with another table.