26
votes
Why is an OR statement slower than UNION?
Jeff already hinted at this, but I feel the need to point out the elephant in the room:
The two queries are not equivalent!
UNION removes all duplicates across the SELECT list.
While the other query ...
- 186k
24
votes
Accepted
Why is an OR statement slower than UNION?
PostgreSQL and many other RDBMSs often struggle with OR predicates.
What often happens, and has happened in this case, is that the compiler decides that it has no way of implementing the two OR ...
- 17.9k
19
votes
Accepted
Are there side effects to Postgres 12's NOT MATERIALIZED directive?
It's explained very well in the documentation.
A useful property of WITH queries is that they are normally evaluated
only once per execution of the parent query, even if they are referred
to more ...
- 31k
14
votes
Accepted
Case-insensitive collation still comparing case-sensitive
The version of ICU shipped with the Windows builds is a pretty old one, so maybe that's the reason.
Try
CREATE COLLATION collat_ci (
provider = 'icu',
locale = '@colStrength=secondary',
...
- 62.4k
13
votes
Handling performance problems with jit in postgres 12
In my opinion changing the default of jit to 'on' in v12 was a mistake, so based on that opinion turning it back off again seems pretty natural to me. I would have objected to the change if I had ...
- 42.5k
13
votes
Accepted
PostgreSQL nondeterministic collations are not supported for LIKE
Assuming that most of the time the custom, non-deterministic collation works for you and you only need something deterministic occasionally, then you can simply provide the deterministic collation ...
- 70.3k
9
votes
Postgres COPY with on conflict ignore - possible?
No. Maybe in some future version. Now about the best you can do is use file_fdw to map the file in as a foreign table, and then INSERT INTO...SELECT...ON CONFLICT DO NOTHING
- 42.5k
9
votes
Accepted
Monitor logical replication using LSN
I used replication on Postgres 12.
On the publisher side there are few things you can check:
pg_catalog.pg_publication;
pg_catalog.pg_publication_tables;
pg_current_wal_lsn();
I'll create a ...
8
votes
Pre Caching Index on a large table in PostgrSQL
You could use the additional module pg_prewarm. Has to be installed once per database. See:
PostgreSQL: Force data into memory
It can "prewarm" tables as well as indexes. To do it for your index:
...
- 186k
8
votes
Accepted
How to predict how much space a VACUUM FULL would reclaim?
You need the pgstattuple extension to get the amount of free space.
So you could run
SELECT t.oid::regclass AS table_name,
s.table_len AS size,
dead_tuple_len + s.approx_free_space AS ...
- 62.4k
7
votes
Accepted
Recursive CTE based on function values significantly slower on Postgres 12 than on 11
Thanks to the helpful guys at the pgbugs mailing list, it turned out that Just-in-time-compilation (some helpful background information to be found here) being turned on by default in PostgreSQL 12 ...
- 499
7
votes
Is there really no SIMPLE way to achieve "recursive lookup" in PostgreSQL?
I think if you get your head round a basic recursive CTE implementation, you won't find them so difficult to understand in future.
I'm going to try and show you how to do the COUNT(*) you mention in ...
- 17.9k
7
votes
High cost in PostgreSQL query
As hinted at in a comment, you shouldn't be looking at the query cost, but rather at its actual run time (or you shouldn't be bothered by any of it at all if the run time is acceptable). The estimated ...
- 28.8k
7
votes
Accepted
Optimizing select count result in Postgresql
From the PostgreSQL Wiki:
The reason is related to the MVCC implementation in PostgreSQL. The fact that multiple transactions can see different states of the data means that there can be no ...
- 19k
6
votes
Accepted
Why isn't it possible to cast to a timestamp in an index in PostgreSQL?
Casts from text to timestamp without time zone are handled by calling the type input function timestamp_in, which is STABLE.
The reason is that timestamp_in supports some other formats too:
SELECT '...
- 62.4k
6
votes
Accepted
Query performance degraded after upgrading Postgres
Which index to use?
While your WHERE conditions are not very selective, the current query plan makes a lot of sense with ORDER BY activity_count DESC LIMIT 101. See:
How to search a table with 80 ...
- 186k
6
votes
Accepted
Why is a GiST index used for filtering on non-leading column?
an index can only be used when we have predicates for the leading (or all) columns.
In Postgres, this rule of thumb is only somewhat applicable to (default) B-tree indexes. See:
Working of indexes ...
- 186k
6
votes
Accepted
How to prevent pg_dump from generating "SET default_table_access_method=heap;"?
No, there is no such way, because downgrading PostgreSQL is not supported.
You'll have to manually edit the dump and remove lines such as that until the dump loads without error. There could be other, ...
- 62.4k
6
votes
Why is an OR statement slower than UNION?
I get mostly the same speed for the EXISTS query either with or without the OR location_type = 2 as long as work_mem is set large enough (more than about 20MB).
For EXISTS without the OR clause, I get ...
- 42.5k
6
votes
Why is this SQL query with UNION significantly faster than the same query without?
It's typically a good idea to split up that ugly OR in to a UNION query. See:
Why is an OR statement slower than UNION?
The first SELECT of the UNION query should melt down to milliseconds with this ...
- 186k
6
votes
Accepted
postgresql database cluster vs one server with many databases
You ask:
I want to know what are the differences between postgreSQL database
cluster (1 server, N databases, N ports) vs one server hosting
multiple databases (1 server, N databases, 1 port)?
Short ...
- 31k
5
votes
merging many jsonb objects using aggregate functions in PostgreSQL?
I had the same problem and this post solved it:
The idea is to create an aggregate:
CREATE AGGREGATE jsonb_object_agg(jsonb) (
SFUNC = 'jsonb_concat',
STYPE = jsonb,
INITCOND = '{}'
);
See the ...
- 159
5
votes
Accepted
Query slow when using function in the WHERE clause
Consider this simplified equivalent:
CREATE OR REPLACE FUNCTION ext.uf_converte_numerico(_input varchar(30))
RETURNS bigint LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
$func$
SELECT NULLIF(...
- 186k
5
votes
Convert SQL server stored procedures to postgreSQL
Unfortunately you'll probably have to handle the stored procedures by hand. If you're lucky, they're simple procedures following standard SQL syntax, and you'll be able to just copy the existing ...
5
votes
Accepted
Use pg_cron to run VACUUM on multiple tables
You can vacuum a list of tables in one command.
vacuum FULL pgbench_accounts, pgbench_history, pgbench_branches, pgbench_tellers;
But hopefully you are not really doing FULL, that would almost surely ...
- 42.5k
5
votes
Accepted
How to get slow query on PostgreSQL?
log_statement being set to all is telling PostgreSQL to log the text of all statements. When used together with log_min_duration_statement, statement text is not repeated in logs, forcing manual ...
- 281
Only top scored, non community-wiki answers of a minimum length are eligible