19

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 with OR keeps them. You have SELECT * FROM geolocations, an no other tables in the FROM list. So if there are no duplicate rows in the table (which is guaranteed ...


12

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 conditions via a single seek, and instead scans the whole index, evaluating the two (or more) conditions on every row. This is despite the more obvious (to a human) ...


11

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', deterministic = false ); This should work with older ICU versions.


6

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 reclaimable FROM pg_class AS t CROSS JOIN LATERAL pgstattuple_approx(t.oid) AS s WHERE t.relkind = 'r' ORDER BY (s.dead_tuple_len::float8 + s.approx_free_space:...


5

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 had been my problem. Running my query with SET jit = off; solved the issue: Without it, my query runs fast as it should.


5

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: SELECT pg_prewarm('test.test_table_idx'); Unless you get index-only scans (which you do not with the index at hand), you might want to prewarm the table as well:...


5

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 a Hash Join. With the OR clause I get Hashed SubPlan. The problem is that while Hash Join knows how to deal with low work_mem by spilling to disk in an ...


4

I had the same problem and this post solved it: https://blog.faraday.io/how-to-aggregate-jsonb-in-postgres/ The idea is to create an aggregate: CREATE AGGREGATE jsonb_object_agg(jsonb) ( SFUNC = 'jsonb_concat', STYPE = jsonb, INITCOND = '{}' ); See the linked article for more details.


4

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(regexp_replace(_input, '[^0-9]+', '', 'g'), '')::bigint; $func$; IMMUTABLE, because it is, and for the reasons Laurenz explained. PARALLEL SAFE in Postgres 10 or later, ...


4

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 been paying more attention at the time (which doesn't mean I would have prevailed, of course). The change seems to harm at least as many people as it helps. And ...


4

I guess you are referring to “returning multiple result sets”. First, the page you are showing is from the PostgreSQL TODO list, which is not a list of new features in v12, but a collection of things that might be useful to implement — a wish list rather than a feature list. Stored procedures have been introduced in v11, but there is no ...


4

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 than once by the parent query or sibling WITH queries. Thus, expensive calculations that are needed in multiple places can be placed within a WITH query ...


4

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 in PostgreSQL But mostly wrong for GiST indexes. The manual: A multicolumn GiST index can be used with query conditions that involve any subset of the index's ...


4

No, there is no way to do that. I would recommend that you create a second PostgreSQL cluster and put the database there. That does not incur a big overhead as far as system resources are concerned, and it will solve your problem.


3

The reason is that both CTEs and temporary tables are private to the backend process that created them, and parallel worker processes are different processes that do not have access to the private resources of the leader process. To make CTE scans parallel safe, CTEs would have to be materialized in shared memory. I would be careful marking a function that ...


3

As documented in the manual "A default namespace specification is not currently supported" You need to give the namespace an alias and use that in the XPath expressions: SELECT xmltable.* FROM xmldata, XMLTABLE( xmlnamespaces('http://x.y.z' as x), '//x:ROWS/x:ROW' PASSING data COLUMNS id int PATH '@id', ...


3

Since the function is VOLATILE (by default), PostgreSQL doesn't know that it will return the same value for every row in central.cliente, so it is evaluated repeatedly. Set the volatility to IMMUTABLE and PostgreSQL knows that it has to be evaluated only once: ALTER FUNCTION ext.uf_converte_numerico(varchar(30)) IMMUTABLE;


3

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 'now'::timestamp; timestamp ---------------------------- 2019-10-09 09:53:32.026673 (1 row) If you know that your input is always an ISO ...


3

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 when needed via the COLLATE clause. For example, in your case, you can use the tr-TR-x-icu collation as follows: select name from testtable1 WHERE name LIKE '%a%' ...


3

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


3

I would convert that value to an integer array, then sort on that array: SELECT number FROM orders ORDER BY string_to_array(number, '-')::int[] LIMIT 10; Online example


3

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 very, very easy steps. We are going to find all Animals that are of the Cat family. Apologies to any taxonomists here. 1. Firstly, we need to define a starting ...


2

As pointed out by @a_horse_with_no_name, bigint is good enought. I think 29 million years is reasonable (hehe). The math checks. Should have done it before.


2

You have to have more patience, scanning a large table takes a while. The classical remedy is to create an index: CREATE INDEX ON "table" (length); That will take a long time itself (and might use considerable disk space), but once you have that index, it will speed up your query (unless it returns so many rows that an index scan is no more efficient than ...


2

jsonb_path_query() is a set-returning function. When putting more than one of those in a SELECT list, this is the expected behavior. See: What is the expected behavior for multiple set-returning functions in SELECT clause? You seem to be looking for a CROSS JOIN instead: SELECT * FROM (SELECT jsonb_path_query(test, '$.title'::jsonpath) AS title FROM ...


2

As pointed out in the answer to my other, more specific question, the reason is Just-in-time compilation. SET jit = false; solves all performance problems in my case.


2

Regardless of whatever issues you might have, pg_cancel_backend cancels the backend's current query. But an idle backend doesn't have a current query, which is what makes it idle. You want pg_terminate_backend. More fundamentally, it seems like you have an application problem or a connection pooler problem, but you haven't described them enough for us to ...


2

Does massive errors count affect the DB performance? Not really. It will make the error log [a bit] larger, because each error gets written there, but that should be trivial in size compared to the database itself. Each of those writes to the log will take [some] time, but not a great deal. What will get noticed is that the Application trying to ...


2

The two tables person and praktikant in your example are two distinct tables. Inserting rows into praktikant does not insert rows in person, but SELECT ... FROM person; is effectively the same as SELECT ... FROM ONLY person UNION ALL SELECT ... FROM praktikant; Look at the EXPLAIN output for both queries to confirm that. Each of these tables has its own ...


2

I wouldn't bother. Postgres has a record overhead of at least 23 bytes per record -- and that's not counting a bitmap to keep track of NULLs, or other block overheads which combined probably average another couple of bytes per record. See https://stackoverflow.com/a/26206031/391445 Your bigint takes 8 bytes of storage, versus 17 for the booleans. Another ...


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