Linked Questions
11 questions linked to/from Can declaring function volatility IMMUTABLE harm performance?
7
votes
2
answers
8k
views
Why isn't to_char IMMUTABLE, and how can I work around it?
How can I index a to_char() of a column?
I have tried:
adam_db=> CREATE INDEX updates_hourly_idx
ON updates (to_char(update_time, 'YYYY-MM-DD HH24:00'));
But got the error:
ERROR: ...
- 11.5k
1
vote
2
answers
2k
views
How can a cheap function in a SELECT make the whole query slow?
I'm using Postgres 13.3 with inner and outer queries that both only produce a single row (just some stats about row counts).
I can't figure out why Query2 below is so much slower than Query1. They ...
- 303
5
votes
1
answer
4k
views
How to select sub-object with given keys from JSONB?
How to extract / get / select a "sub object" from a json(b) object in Postgres?
There seems to be a lot of info that get me almost all the way there but not quite. Lots of stuff about ...
- 153
3
votes
2
answers
3k
views
Containment check tstzrange @> timestamptz not using btree or gist index
Schema:
Column | Type
----------------------+--------------------------
id | integer
event_id | integer ...
- 1,145
1
vote
1
answer
2k
views
PostgreSQL functions inside where clause
I have table like this:
table a (
id
--
p1 text,
p2 text,
p3 text
)
When I write some function, which use this table, I need to write code like this
create or replace function ...
...
- 113
3
votes
2
answers
736
views
Using PostgreSQL functions instead of joining?
I don't have a question on how to do something but if I should. Sorry if there's a more appropriate forum.
I have a function:
CREATE OR REPLACE FUNCTION get_name(user_id UUID) RETURNS TEXT AS $$
...
- 163
3
votes
1
answer
844
views
How can I find out if a SQL function can be inlined?
In this answer Erwin breaks down IMMUTABLE functions into those that can be inlined, and those that can't. He has an example with to_char() that shows an IMMUTABLE function over to_char() will ...
- 62.2k
2
votes
1
answer
1k
views
What is meant by a non-volatile expression in Postgresql?
Reading this post, the author references a "non-volatile" expression, after researching a bit, I'm still not sure what it is. What is meant by a non-volatile expression in Postgresql?
- 267
0
votes
3
answers
677
views
Why is this PostgreSQL volatile function faster than an inline subquery?
The following examples have been tested in PostgreSQL versions 11 and 12.
A few of the tables involved have around 2M rows, others not more than 250k.
I have very big and nested queries like this (not ...
- 101
1
vote
1
answer
110
views
Is it possible to reduce the duplication in this simple query?
I'm new to SQL and so this is probably a stupid question but can I reduce the duplication in this query? My actual query involves more complex expressions. Right now I'm just doing string ...
- 13
2
votes
1
answer
53
views
Function in JOIN on DISTINCT values executes for each row instead of distinct parameters
I'm JOINing function execution using some of the values from table, expecting that it will execute only as many times as there are unique sets of parameters, but instead it executes for each row.
...
- 173