Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [postgresql-12]

Specifically for PostgreSQL version 12

243 questions
21
votes
3 answers

Why is an OR statement slower than UNION?

Database version: PostgreSQL 12.6 I have a table with 600,000 records. The table has the columns: name (varchar) location_type (int) enum values: (1,2,3) ancestry (varchar) Indexes: ancestry (btree) The ancestry column is a way to build a tree…
Mohamed Hussein
  • 321
  • 2
  • 9
17
votes
1 answer

Handling performance problems with jit in postgres 12

tl;dr: What is the best way to handle performance decrease caused by jit? Background: Lately I have migrated from postgres 11 to 12 and noticed, that some queries / procedures run considerably slower. I did some research and tested different…
Andronicus
  • 271
  • 1
  • 2
  • 7
15
votes
1 answer

PostgreSQL nondeterministic collations are not supported for LIKE

I am using Postgresql v12. I created a collation like this: CREATE COLLATION ci (provider = icu, locale = 'tr_TR', deterministic = false); I used that collation in a table: create table testtable1 ( id serial primary key, name text …
Banu Akkus
  • 389
  • 1
  • 3
  • 11
14
votes
3 answers

Are there side effects to Postgres 12's NOT MATERIALIZED directive?

I was doing some performance benchmarking on some of my company's SQL, comparing PG10 to PG12. We use a lot of CTEs in our code, and PG12 didn't natively optimize the CTEs, so the performance was the same between PG10 and PG12. My next experiment…
sorrell
  • 243
  • 1
  • 2
  • 6
11
votes
1 answer

Postgres COPY with on conflict ignore - possible?

I want to add on conflict ignore to the Postgres copy command. I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax. But I wanted to know if this is possible directly from COPY?
Nir
  • 529
  • 2
  • 11
  • 27
10
votes
1 answer

Case-insensitive collation still comparing case-sensitive

I am currently trying to create a table with a text column which will compare case insensitive by default. This is because we have a third party program that executes a search on our database. The SELECT statements used by this program can not be…
Amelia B
  • 211
  • 2
  • 7
8
votes
1 answer

FATAL 53300: Remaining connection slots are reserved for non-replication superuser connections

I have a PostgreSQL 12.1 database system (I'll refer to it as PGSQL) running on a remotely hosted VM server (Windows Server 2019). We upgraded the server OS and PGSQL a couple of months ago. Everything has been running more-or-less normally since…
G_Hosa_Phat
  • 445
  • 2
  • 5
  • 16
8
votes
2 answers

merging many jsonb objects using aggregate functions in PostgreSQL?

Is there a standard function in PostgreSQL (as of 12.x) to concatenate or merge many jsonb objects in a database column into a single jsonb object? I know there is a the || operator since PostgreSQL 9.5 to merge two jsonb objects. But I need to…
tinlyx
  • 3,810
  • 14
  • 50
  • 79
7
votes
0 answers

When does the PostgreSQL 12 CTE materialized option provide an optimization advantage?

I have been reading about PostgreSQL's new CTE feature - the MATERIALIZED or NOT MATERIALIZED keyword - which can offer additional optimization opportunities under certain circumstances, provided that it is safe to do so. A comment on this…
Zeruno
  • 547
  • 1
  • 4
  • 15
7
votes
1 answer

Recursive CTE based on function values significantly slower on Postgres 12 than on 11

Following up on my question about some queries in Postgres 12 being slower than in 11 I think, I was able to narrow down the problem. It seems like one recursive CTE based on function values is the problematic spot. I was able to isolate a rather…
cis
  • 499
  • 6
  • 19
7
votes
1 answer

Upgrade from Postgres 11 to Postgres 12 made some queries 300x slower, probably due to new CTE handling

My application currently uses PostgreSQL 11.6. Today, I have tested PostgreSQL 12.1 on a virtual machine and the results were shocking: One important query which takes 100ms on version 11 (same VM) now takes about 36s on Postgres 12. That's more…
cis
  • 499
  • 6
  • 19
6
votes
1 answer

How to get slow query on PostgreSQL?

Basically I want to log those query which is taking more than 300ms to execute. I don't want to log those query which is less than 300ms. But at PostgreSQL showing all type of log whether it is below 300ms or above 300ms. I configured below…
Sheikh Wasiu Al Hasib
  • 273
  • 1
  • 2
  • 14
6
votes
2 answers

Convert SQL server stored procedures to postgreSQL

I have a SQL Server database that I am migrating to PostgreSQL 12.0. I have managed to migrate the schema and the tables. The applications connecting to the database rely a lot on stored procedures, which also need to be translated to…
Crispin
  • 81
  • 1
  • 2
  • 4
6
votes
1 answer

How to predict how much space a VACUUM FULL would reclaim?

Is there a way to determine in advance how much disk space a VACUUM FULL on a particular table would return to the OS? Therefore you can decide whether it's worth the cost in doing so. Bonus if there's a simple query to do this for every table in a…
OrangeDog
  • 338
  • 1
  • 3
  • 13
6
votes
1 answer

Why is a GiST index used for filtering on non-leading column?

I always learned and understood that an index can only be used when we have predicates for the leading (or all) columns. Now, to my surprise, I noticed that a GiST index is used in the following query. Why is that? Is this a special feature of GiST…
Kejlo
  • 63
  • 4
1
2 3
…
16 17