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


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


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

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

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

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


2

Unless the table is only ever accessed via stored procedures or other programmed logic and you have that set to audit access, and measures are taken to stop access by more direct means, then you probably can't. DBMSs usually don't log read access in a manner that you could query this way, if at all.


2

the execution plan on Postgres 12 shows it went parallel and apparently It made it worse , where in Postgres 9 it went single threaded. seems like postgres overestimated in every single step and eventually thought that is too much work and consequently it went parallel and made a huge scene for nothing. probably If you update your stats and probably rebuild ...


2

I see an elephant in the room: ... and deleted_date is null There can be rows with non-null deleted_date, which are ignored by your test with SELECT but still conflict in the unique index on (feed_id,feed_listing_id). Aside, NOT IN (SELECT ...) is almost always a bad choice. Even if it's not going to break completely with all NOT NULL columns in your case, ...


2

Postgres can truncate a date to a certain precision: SELECT DATE_TRUNC('hour', creation_date) FROM demo So all you have to do is group and count it: SELECT DATE_TRUNC('hour', creation_date), COUNT(*) FROM demo GROUP BY DATE_TRUNC('hour', creation_date)


2

You can force it not use that index by rewriting the join condition to gtf.descendant_id = gv.garage_id + 0. Whether it will then switch to the good plan, or just switch to a different bad plan is not something we can predict. You will have to try it and see. If you want use to analyze why it is making the wrong choice, you will have to show us the ...


2

The extra times goes to the overhead of plpgsql, which is not small. With RETURN QUERY, it first reads all of the tuples into a tuple store, and then reads them back out again. In this case, I think that that accounts for most of the additional time. Note that this extra overhead will be proportional to how rows are returned (not how many are inspected but ...


2

I had the same problem, it's already solved: https://github.com/2ndQuadrant/repmgr/issues/673 Issue has been reproduced and a fix applied, which will be in the upcoming 5.2.1 release.


2

You can create a domain based on your type. create domain checked_dimensions as dimensions constraint check_size check ( (value).w <= 10 ); Note the parentheses around the keyword value. They look redundant but are required in this case. Then use the type checked_dimensions as your column type


1

You can simply add a new column called oid that is automatically filled: ALTER TABLE test ADD oid bigint GENERATED ALWAYS AS IDENTITY NOT NULL; Using an identity column like this has the advantage that you get an error if you by mistake try to INSERT a value into the column manually.


1

The two queries are not equivalent. The first query without view runs window functions after applying the WHERE clause. The second query on the view runs window functions before applying the WHERE clause. This can lead to different results. And (obviously) to different query plans. Your second view peniot_json.all_pde_data does not use window functions (or ...


1

If you have several times the same parameter in postgresql.conf, the last value will override the previous ones. I normally advise keeping the postgresql.conf file as short as possible to prevent confusion. See here. My guess is that you misspelled something on the line you added. To be sure, you can look at Postgres logfiles which should tell you exactly ...


1

postgres peer authentication depends on the linux/unix user that runs psql being the same as user in the -U postgres parameter. As you are running on root locally this isn't true. Having a postgres user outside the container won't necessary map to the postgres user inside the container. Recommend changing authentication mechanisms


1

The entire query is planned up front, and at the time of planning it doesn't know what value will be found in CHECKPOINT1_PKEVENT. It makes the generic assumption that the inequality will match 1/3 of the rows, which is obviously quite wrong. When faced with this situation, I usually just have the client software run the queries separately, stuffing the ...


1

Current version of TimescaleDB, which is 2.0.0, doesn't support push down of aggregates to data nodes (see limitations) and all joins are performed on the access node. So for the queries, which join a distributed hypertable with non-timeseries data (which, I guess, are stored in normal tables), the data will be brought to the access node for the join. Thus ...


1

No. Maybe in some future version. Now about the best you can do is use file_fdw to map the file in as a foreign table, and then INSERT INTO...SELECT...ON CONFLICT DO NOTHING


1

When you create a temporary table in PostgreSQL, only the connection that created that temp table will be able to access it because it is generated in a unique schema for that connection. This is to avoid the very conflicts you're asking about (by using schema separation). This StackOverflow Answer provides more details regarding this. You can also read more ...


1

You need to read the documentation for the version you are using. Since v12, -f - is mandatory to get output to go to stdout. Having that behavior be obtained just by omitting -d was considered to be confusing. But since you want the output to go to a file, just name that file: pg_restore -Fd mydirectory -t sometable -f table.sql


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