5

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 ...


2

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 ...


2

this should do what you want update dst set counter = dst.counter + src.counter from src 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 from src on conflict (user_id) do update set counter = dst....


1

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, ...


1

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&...


1

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 ...


1

Set your default privileges for the schema using ALTER DEFAULT PRIVILEGES


1

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: SHOW timezone; TimeZone --------------- Europe/Vienna (1 row) SELECT TIMESTAMP WITHOUT TIME ZONE '2020-08-01 12:00:00' AT TIME ZONE 'UTC'; ...


1

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; ts_rank_cd --------------------- 0.10000000149011612 (1 row) That explains what you observe, because the real is cast to double precision if you compare it ...


1

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 ...


1

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 ...


1

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 ...


1

You could do something like this: CASE LEFT ( to_char (NULLIF(coalesce(0::numeric,0) - (coalesce(26776.94::numeric,0) - (coalesce(2760::numeric,0) + (coalesce(1800::numeric,0)))),0)::numeric/3600,'FM99,999,999,999.00'))::VARCHAR ,1 -- <<<---- the '1' gets the minus sign from the string! ) = '-' THEN NULL ...


Only top voted, non community-wiki answers of a minimum length are eligible