20

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, and no other tables in the FROM list. So if there are no duplicate rows in the table (which is ...


13

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

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


6

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


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


6

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


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.


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

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

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

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


4

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


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.


4

It's typically a good idea to split up that ugly OR in to a UNION query. See: Why is an OR statement slower than UNION? The first SELECT of the UNION query should melt down to milliseconds with this partial multicolumn index: CREATE INDEX ON changes (user_id, counter) WHERE type IN (1, 3); And after adding ORDER BY counter LIMIT 100. Since the outer ...


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

There is method called continuous Wal_Archiving, that can simulate The Diff backups in MSSQL You run pg_basebackup instead of pg_dump, then ship the WAL_Logs to the backups server. To recover from a failure, restore the last good backup and replay the WALs from the point in time from the last backup. Depending on configuration can be better than DIFF ...


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

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


3

No, there is no such way, because downgrading PostgreSQL is not supported. You'll have to manually edit the dump and remove lines such as that until the dump loads without error. There could be other, more complicated problems than new parameters, see this question for an example in an older version. If the dump is large, you could split it in three parts: ...


3

There can be many reasons for the switched query plan. A very bad plan typically indicates inaccurate column statistics and / or cost constants. It starts with the bad estimate for the index scan on product_tracking_feed_gid_idx: (cost=0.00..2183.11 rows=71806 width=0) (actual time=124.666..124.666 rows=1799676 loops=2) Produces many more rows than Postgres ...


3

There is no stock way to micromanage shared buffers that way. And in my experience is not a very fruitful area of effort. I've looked at the space used by the tables and those which use the most are not really relevant for us at the moment. Are you looking at shared_buffers itself (with pg_buffercache), or are you looking at the on-disk sizes of the ...


3

The tolerable one with the OR got lucky, because it found 100 matching rows with types of 1 or 3, before it found any of type 2 which had to be checked against the other table. The intolerable one apparently did have to do the check against the other table, and it does it in a very slow way, by looping over all the rows in it. Now it should use a hashed ...


3

We have added arguments to pg_stat_statements_reset during postgresql 12 developing cycle (in fact, in version 1.7 of the extension itself). These new arguments have default values, but anyway foo() and foo(param int DEFAULT 0) are different functions from postgresql point of view. You need replace this GRANT to grant all on FUNCTION pg_stat_statements_reset(...


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