No, it's not necessary. Statistics on the main table are not affected by indexes at all.
One thing to observe: expression indexes introduce new "derived" columns, and Postgres gathers separate statistics for those. Removing such indexes also removes the special statistics - which may lead to different query plans, beyond the fact that the index is ...
The main reason for the slowness is that you aggregate over the big table from scratch for every iteration of the lateral sibquery. Compute earliest review & current total count per app in a CTE once and base the lateral subquery on it. I discussed that and some other optizations under your predating related question on SO:
Get apps with the highest ...
this should do what you want
set counter = dst.counter + src.counter
where src.user_id = dst._user_id;
If dst.user_id is the primary key (or defined as unique), you can combine both into one statement:
insert into dst (user_id, counter, text)
select user_id, counter, text
on conflict (user_id)
set counter = dst....
I took the correct answer above and made it into a function that generated the script passing in the schema name and a search pattern as params. I keep a few of these in my db during design phase but either lock them down or remove them from production.
CREATE FUNCTION extfunc.systool_generatescript_functiondelete(
schemaname character varying,
I would just duplicate the expression. It is possible to structure your query so that you only define the expression once, but it will end up being more verbose, as it requires that you define the field you're WHEREing as a column in a subquery and then do the WHERE on the outer field.
SELECT * FROM (
SELECT *, ("last done" + "auto time span&...
The transaction won't be "auto rolled back", it will be marked as failed, and the only option you have, is to do a rollback.
If you did not run rollback in the function but simply return from it in case of an error, then the UPDATE following the function call would result in "current transaction is aborted, commands ignored until end of ...
timestamp without time zone is time zone agnostic, so it is not adjusted to any time zone on display. This data type just doesn't have a time zone.
You can convert it to a certain time zone with AT TIME ZONE:
SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC';
ts_rank_cd yields a real. If you cast this to double precision, the result becomes:
SET extra_float_digits = 3;
SELECT ts_rank_cd(to_tsvector('cat'), to_tsquery('cat'))::double precision;
That explains what you observe, because the real is cast to double precision if you compare it ...
If two programs don't need to access each other's data, put the data into different databases.
If they need to access each other's data, using schemas like you describe is a good idea.
For security reasons it might be a good idea to use one schema for objects available to both programs and a schema each for data that only one program uses. That way you can ...
It's not a schema in my database.
Yes, it is. And it's neither "invisible" nor undocumented:
Quote from the manual
In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the ...
Define a StatusType that will migrate through to a StatusLevel and provide a mechanism for a CHECK constraint.
DML (I'm doing this freehand, there may be syntax errors lurking):
CREATE TABLE StatusType
StatusTypeCd TEXT NOT NULL
,Description TEXT NOT NULL
,CONSTRAINT PK_StatusType PRIMARY KEY (StatusTypeCd)
INSERT INTO StatusType VALUES ...
You could do something like this:
to_char (NULLIF(coalesce(0::numeric,0) -
,1 -- <<<---- the '1' gets the minus sign from the string!
= '-' THEN NULL