Linked Questions

8 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: ...
Adam Matan's user avatar
  • 11.7k
5 votes
1 answer
6k 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 ...
AlexanderF's user avatar
1 vote
2 answers
3k 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 ...
LaVache's user avatar
  • 323
3 votes
2 answers
3k views

Containment check tstzrange @> timestamptz not using btree or gist index

Schema: Column | Type ----------------------+-------------------------- id | integer event_id | integer ...
Gajus's user avatar
  • 1,195
2 votes
1 answer
3k 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 ... ...
Andrew Bystrov's user avatar
3 votes
2 answers
971 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 $$ ...
agent nate's user avatar
3 votes
1 answer
1k 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 ...
Evan Carroll's user avatar
  • 63.7k
2 votes
1 answer
2k 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?
Nona's user avatar
  • 267
0 votes
3 answers
759 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 ...
AndreFeijo's user avatar
1 vote
1 answer
135 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 ...
user225326's user avatar
2 votes
1 answer
54 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. ...
Łukasz Kamiński's user avatar