10 votes
Accepted

Do datatypes of columns affect query performance?

Yes. And probably a lot. You never gain performance using character(n)(alias char(n)). You don't gain anything at all because that type is outdated, mostly useless, and discouraged. Related: Would ...
user avatar
6 votes
Accepted

PostgreSQL crash due to lack of device space

You cannot know which directory belongs to which database without a look at pg_database or the file for that table (probably /var/lib/pgsql/11/data/global/1262). But you cannot start deleting files or ...
user avatar
  • 32.1k
3 votes
Accepted

Is it safe to use default value with not null when adding a new column?

That statement is safe. An ALTER TABLE will never lock "the database", it will only lock the table. Postgres will not actually rewrite the table because you provided a constant value (false) ...
user avatar
3 votes
Accepted

Why is the optimizer not using the clustering index on my table?

Your filter l_shipdate<='31/08/1998' Is not very selective, we can see from the plan that it was responsible for removing only 293,696 rows and it ended up needing to use 19,701,655. If it was to ...
user avatar
  • 2,289
2 votes
Accepted

How to incrementally optimize a `COUNT(*) ... GROUPBY` query in PostgreSQL?

Why do you have (event = 'view load' OR event = 'view:loaded')? Do those actually mean two different things, or do you just have dirty data with two difference spellings for the same meaning? Your ...
user avatar
  • 31.5k
2 votes
Accepted

How to fix unicode REPLACEMENT CHARACTER � in postgres database

That character (0xEFBFBD in UTF-8) really is Unicode 0xFFFD, the “replacement character” �. So if that's not what you want, the problem must have occurred when you inserted the data into the database. ...
user avatar
  • 32.1k
1 vote

"column new.total does not exist" error while creating a trigger

The Hint in the error message suggests that you have a column of the name "new.total" - containing the reserved word "new", and a dot (!). The otherwise illegal identifier forced ...
user avatar
1 vote
Accepted

Is it possible to reference an inserted column within the same INSERT?

You probably also saw the hint: HINT: There is a column named "a" in table "qwe", but it cannot be referenced from this part of the query. You cannot reference a column value ...
user avatar
1 vote
Accepted

Marking/retrieving a long array of numbers as "true/false" in postgres

Both, bit and bit varying type can store a bit mask of 10 million bits (the upper limit seems to be 83886080). But in order to be able to set/get bits in there, you need to pre-initialize the value. ...
user avatar
1 vote

How to enforce only one NULL value per unique combination of some other columns?

I want to have only one null value for some_value when enabled is false. A minimalist partial unique index can do that: CREATE UNIQUE INDEX a_some_value_not_enabled_uni_idx ON a (1) -- constant ...
user avatar
1 vote
Accepted

Express a predicate DATE >= X and DATE < Y in form of DATE in [X,Y)

Use a date range daterange(x, y, '[)') @> the_date The @> operator tests if the value on the right hand side is contained in the range on the left hand side
user avatar
1 vote
Accepted

Forcing a sequential scan over the index scan

set enable_indexscan TO off; set enable_bitmapscan TO off; solves
user avatar
  • 95
1 vote

Passing dynamic filter to the Postgresql stored procedure

Your issue is trying to use single quotes within a string: 'name='jhon'' Here the second quote is not taken as part of the string, but the end of it. The parser then sees jhon and doesn't know what ...
user avatar
1 vote
Accepted

Autovacuum does not clear the database

It will take a very long time to vacuum a table of that size with the those settings (assuming the ones you don't show are at their defaults). If the live tuples are really 1% of the dead tuples, the ...
user avatar
  • 31.5k
1 vote

Postgres equivalent of Oracle's RMAN.VALIDATE_DATABASE

The procedure tries to ensure that the backup pieces available can be used to restore the database. I don't know if there's any equivalent for PostgreSQL. However, the only way to be absolutely ...
user avatar
  • 6,469
1 vote
Accepted

Why parallel aggregation is not used for subquery when other subquery using non parallel aggregation?

No, that is working as expected. If there is a parallel unsafe function used anywhere in a query, that query cannot use parallelism, period. In the words of the documentation: When a query contains ...
user avatar
  • 32.1k

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