16

Probable bug on 9.6 and 9.6.1 This completely looks like a bug to me... I don't know why it happens, but I can confirm that it happens. This is the simplest found setup that reproduces the problem (in version 9.6.0 and 9.6.1). CREATE TABLE users ( id SERIAL PRIMARY KEY, email TEXT NOT NULL, column_that_we_will_drop TEXT ) ; -- Function that ...


14

BTree My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can't assume the table is physically sorted). If the BTree is huge I end up having to read both the index and the parts of the table that the index points too... Not necessarily — Having a btree index that is 'covering' will be the ...


13

For only 400 stations, this query will be massively faster: SELECT s.station_id, l.submitted_at, l.level_sensor FROM station s CROSS JOIN LATERAL ( SELECT submitted_at, level_sensor FROM station_logs WHERE station_id = s.station_id ORDER BY submitted_at DESC NULLS LAST LIMIT 1 ) l; dbfiddle here (comparing plans for this query, ...


12

No, there is no 1-byte integer in the standard distribution of Postgres. All built-in numeric types of standard Postgres occupy 2 or more bytes. Extension pguint But yes, there is the extension pguint, maintained by Peter Eisentraut, one of the Postgres core developers. It's not part of the standard distribution: In addition to various unsigned integer ...


11

What's happening here is the Nested Loop is way off on one side. Nested Loops work really well when one side is very small, such as returning one row. In your query, the planner fumbles here and estimates that a Hash Join will return just one row. Instead, that Hash Join (property_id = id) returns 1,338 rows. This forces 1,338 loops to run on the other side ...


9

The documentation on the RETURNING clause says: An expression to be computed and returned by the INSERT command after each row is inserted. The expression can use any column names of the table named by table_name. Write * to return all columns of the inserted row(s). This clearly does not apply for columns from an other table. Though I don't ...


8

You need to install a specific EXTENSION in your database: CREATE EXTENSION btree_gist ; According to PostgreSQL documentation on btree_gist: btree_gist provides GiST index operator classes that implement B-tree equivalent behavior for the data types int2, int4, int8, float4, float8, numeric, timestamp with time zone, timestamp without time zone, time ...


8

This may or may not work - I'm basing this off a gut feeling that it's joining your tables before the group and filter. I suggest trying the following: filter and group using a CTE before attempting the join: with __posts as( select user_id, count(1) as num_posts from posts group by ...


8

1. You know the column names ... For Postgres 9.6 or later, use num_nulls() WITH t(a, b, c, d, e) AS ( VALUES (NULL ,'hi',2,NULL,'null') , ('' ,'hi',2,3,'test') , (NULL ,'hi',2,3,'null') ) SELECT num_nulls(a,b,c,d,e) FROM t; Returns your desired result exactly, works for any mix of data types. The manual: num_nulls(VARIADIC "any") .....


7

It is not allowed to have arbitrary expressions in FOREIGN KEY constraints, only columns. That's why you get an error when you try the 2nd approach. You can however, use a VIEW to access the table: CREATE VIEW v_session_groups (group_id, session_range, description) AS SELECT group_id, int4range(first_session, last_session, '[)'), ...


7

The following code will be able to use a GIN index on the jsonb column: SELECT COUNT(*) FROM results WHERE subjects @> '[{"subject": "B/MATH", "grade": "A"}]' The difference to your example is that I don't unpack the array, I just query the jsonb column directly. The GIN index can be used for the @> operator on jsonb columns, but it can't be used for ...


7

You can do that in a single query using a writeable CTE: with updated as ( UPDATE bankdetails SET bank_details = 'mysore', "PAN"= 123, bank_acc = 456, "UAN" = 789, tax = 'myy' WHERE bankdetails.user_id = 79 returning * ) INSERT INTO bankdetails (user_id,bank_details,"PAN",bank_acc,"UAN",tax) ...


7

The types of indexes in Postgresql are stored in the pg_am catalog table. So to get a list of all tables and their indexes with the index type (or access method ("am") as PostgreSQL calls it) you can run the following SELECT tab.relname, cls.relname, am.amname FROM pg_index idx JOIN pg_class cls ON cls.oid=idx.indexrelid JOIN pg_class tab ON tab.oid=idx....


7

An index on (point_id, datetime, value) will likely speed up the query, as it will only have to do an index seek, read only the relevant part of the index and also have available (from the index) all the values of the value column to calculate the MIN and MAX. An index on (A) (point_id) or (B) (point_id, datetime) would need to also find the read the ...


6

When running into an error like this, be sure to run barman switch-xlog --force --archive <server_name> as noted in the barman documentation to verify the WAL archiving process.


6

Add a conditional unique index CREATE UNIQUE INDEX IndexName ON foo (fk) WHERE (int>0); dbfiddle here


6

Besides btree and brin which seem the most sensible options, some other, exotic options which might be worth investigating - they might helpful or not in your case: INCLUDE indexes. They will be - hopefully - in the next major version (10) of Postgres, somewhere around September 2017. An index on (a) INCLUDE (b) has the same structure as an index on (a) but ...


6

Try the classic way: create index idx_station_logs__station_id on station_logs(station_id); create index idx_station_logs__submitted_at on station_logs(submitted_at); analyse station_logs; with t as ( select station_id, max(submitted_at) submitted_at from station_logs group by station_id) select * from t join station_logs l on ( l.station_id = ...


6

It turns out that the vast majority of my write throughput was coming from the stats collector. Because of the very high number of relations in my database, the stats data is unusually large. I was able to diagnose the problem by temporarily clearing the stats: SELECT pg_stat_reset() Which led to an immediate, dramatic drop in my write throughput. To ...


6

The advantage of JSON is versatility: you can add any keys without changing the table definition. And maybe convenience, if your application can read and write JSON directly. Separate columns beat a combined json or jsonb column in every performance aspect and in several other aspects, too: More sophisticated type system, the full range of functionality (...


6

It's not sufficient that 195.249.206.131 resolves to kommune.horsholm.dk that matches the wildcard. After checking that, Postgres will try to resolve kommune.horsholm.dk and find that it doesn't resolve to 195.249.206.131 (as it doesn't resolve at all in this particular case) $ host kommune.horsholm.dk Host kommune.horsholm.dk not found: 3(NXDOMAIN) Doc ...


5

Not possible, because There is currently no direct way in Postgres 9.6, because: In the UPDATE, only the special EXCLUDED row is visible (in addition to the updated row). There is no FROM clause allowed to join in additional tables. The EXCLUDED row is exactly the state of the would-be inserted row that was rejected by conflict. Exactly the columns of the ...


5

The Append has to do some buffer management. It also makes a lot of calls to some clock function (gettimeofday, for example), to satisfy the timing component of EXPLAIN ANALYZE. That overhead might be the dominant time sink. In your case, the Result node is computing the result of the SUBSTR function. You can see this is in the Output field of the verbose ...


5

Concurrent reads are not a problem. Writers don't block readers and vice versa in the default READ COMMITTED isolation level. Enclose DELETE and INSERT in a single transaction to make the operation atomic (all applied or nothing). If there can be multiple transactions trying to write at the same time, that's a game changer. A single transaction protects you ...


5

The database the dump file was taken from has a certain foreign key constraint defined. But your target database does not. Probably because someone went and added it to the dumped database since the last dump. Since you specified --clean, it is trying to drop all the objects that exist in the dump file from your target database before recreating them. It ...


5

You need a Entity Relation Diagram (ERD) generator. The most popular and advanced one that I know of that is FOSS is SchemaCrawler. It does everything you want anyway. Though the diagrams are generated with Graphviz (so don't expect Visio quality). You may want to go scavenger hunting through different graphviz viewers. This one using d3 seems to be pretty ...


5

The table may be small, but as long as Postgres expects roughly 0 rows, chances are it is going to chose a different query plan than for roughly 40 rows - for which the same query plan is not as efficient. Since joins multiply result rows rather than just adding to them, the 40 rows in the tiny table can have a massive effect when joined to big tables with ...


5

We also don't ever update primary keys. In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking: FOR NO KEY UPDATE Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to ...


5

If you have superuser access, you can use: select * from pg_ls_dir('pg_xlog'); which will return one row for each file in the directory pg_xlog. As the size for a WAL segment is fixed, you can easily calculate the total size by multiplying the number of rows by 16MB: select count(*) * 16 * 1024 * 1024 as total_size from pg_ls_dir('pg_xlog') as t(fname) ...


5

Certain graphical DB clients will show you the image when going to the table data view. In plain query output (for example in psql), though, you cannot expect images to appear. Otherwise, you have to 'decode' the bytea. Here is an example solution (written in PHP) on gis.stackexchange.com: $conn = pg_connect("..."); $res = pg_query($conn, "SELECT ...


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