Hot answers tagged

36

To elaborate on @alci's answer: PostgreSQL doesn't care what order you write things in PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone. The order in which joins are written is also ignored up to the configured join_collapse_limit; if there ...


20

On 18th of November, 1883 at 12:00 (new time), standard time was adopted by the American railroads. This means that before that time, Los Angeles used actual local time, based on mean solar time. After that, it was moved to its local time zone, which, being an integral offset of hours from the Greenwich Mean Time, was slightly different from the previous ...


18

In PostgreSQL 9.6 there will be a new version of pg_trgm, 1.2, which will be much better about this. With a little effort, you can also get this new version to work under PostgreSQL 9.4 (you have to apply the patch, and compile the extension module yourself and install it). What the oldest version does is search for each trigram in the query and take the ...


16

Relational databases are designed around joins, and optimized to do them well. Unless you have a good reason not to use a normalized design, use a normalised design. jsonb and things like hstore are good for when you can't use a normalized data model, such as when the data model changes rapidly and is user defined. If you can model it relationally, model ...


14

SQL is a declarative language: you tell what you want, not how to do it. The RDBMS will choose the way it will execute the query, called the execution plan. Once upon a time (5-10 years ago), the way a query was written had a direct impact on the execution plan, but nowadays, most SQL database engines use a Cost Based Optimizer for planning. That is, it ...


13

There's really no one 'best way' to store time series data, and it honestly depends on a number of factors. However, I'm going to focus on two factors primarily, with them being: (1) How serious is this project that it deserves your effort to optimize the schema? (2) What are your query access patterns really going to be like? With those questions in ...


12

How does PostgreSQL knows by just a bitmap anything about rows' physical order? The bitmap is one bit per heap page. The bitmap index scan sets the bits based on the heap page address that the index entry points to. So when it goes to do the bitmap heap scan, it just does a linear table scan, reading the bitmap to see whether it should bother with a ...


12

Unsurprisingly, the manual is right. But there is more to it. For one, size on disk (in any table, even when not actually stored on disk) can be different from size in memory. On disk, the overhead for short varchar values up to 126 bytes is reduced to a 1 byte as stated in the manual. But the overhead in memory is always 4 bytes (once individual values are ...


12

The query you have is basically correct. The only mistake is in the second (recursive) part of the CTE where you have: INNER JOIN descendants d ON d.parent_id = o.object_id It should be the other way around: INNER JOIN descendants d ON d.object_id = o.parent_id You want to join the objects with their parents (that have already been found). So the ...


12

tl;dr: The first process that reads data after it is committed will set hint bits. That will dirty the page, creating write activity. The other thing VACUUM (but not other commands) does is marks the page as all-visible, if appropriate. VACUUM will eventually have to hit the table to freeze the tuples. The work that needs to be done after an insert isn't ...


11

This happens when not having USAGE privilege on any of the schemas of search_path. By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public; This is required when it's not desirable that people peek into other people schemas,...


11

PostgreSQL does not return these instead of boolean values. It is some clients (for example, psql and pgAdminIII) which represents TRUE with t and FALSE with f - try the same query in another client and you will see something else. See, for example, what DBVisualizer gives you: I guess the reason for showing t and f is simply sparing space in a command-...


11

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing. General difficulty The Problem is: predicates on some columns, ORDER BY on a different column. In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...


10

My locale settings were not properly configured when PostgreSQL was installed. Purging and reinstalling didn't help. I followed the instructions here and that did the trick for me. Essential parts of the linked information reproduced below: The problem showed itself in the following manner: warning: Please check that your locale settings: LANGUAGE = (...


10

As Daniel Vérité mentioned there doesn't seem to be a generic solution. When loading data into a table from a file the following technique can be used to get the progress of the load. COPY command console progress bar Create an empty table. CREATE TABLE mytest (n int); Create a data file with 10 million lines for loading into the table. $ seq 10000000 &...


10

According to the docs PL/pgSQL Under the Hood, you can use the configuration parameter plpgsql.variable_conflict, either before creating the function or in the start of the function definition, declaring how you want such conflicts to be resolved (the 3 possible values are error (the default), use_variable and use_column): CREATE OR REPLACE FUNCTION pg_temp....


10

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table: I believe the issue with this is that a SQL function will do parsing (and ...


10

Here is what I do in such cases, usually some of this helps: Look at the whole query and try to remove unneeded tables from it. Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS. Try to increase planner constants so the server can put more effort into planning phase. You can do ...


10

If you have a serial column or an integer one that's automatically populated with a nextval (so that you are never supposed to insert new rows with an explicit value for that column), you could additionally check whether the value of that column is greater than a specific value: ( (("qb_id" IS NOT NULL) :: INTEGER + ("xero_id" IS NOT NULL) :: INTEGER + ...


9

This query: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; says: For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB)...


9

The data type uuid is perfectly suited for the task. It only occupies 16 bytes as opposed to 37 bytes in RAM for the varchar or text representation. (Or 33 bytes on disk, but the odd number would require padding in many cases to make it 40 bytes effectively.) And the uuid type has some more advantages. Example: SELECT md5('Store hash for long string, maybe ...


9

Hardware problems... This log entry: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. can be a result of at least two different underlying problems. The first one is a bad executable or failing hardware - this is ...


9

You're limiting the resultset of the aggregate function count(), which will always return 1 row. IE: It's limiting the output of the count(*) function, rather than LIMITing just FROM data WHERE datetime < '2015-09-23 00:00:00'. Basically: Postgres reads all the rows FROM data WHERE datetime < '2015-09-23 00:00:00' Postgres then count(*)s them ...


9

Generally you would not install Pgpool on the backend servers. What you see in your picture is the most common configuration. Pgpool is a standalone server which essentially sits in front of the databases. The two Postgres servers are often configured with streaming replication; with one being the master and the other the slave. This allows Pgpool to load ...


9

A variation on @a_horse_with_no_name's answer would be to first create the table with the constraint and then insert into the table, still using a transaction, so everything rolls back in case of constraint violations. This is something you should consider if the rows to be inserted are a lot (i.e. in the hundreds of thousands or millions). If it's ...


9

Just add the constraint as NOT VALID From the manual: If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, [...] and they'll fail unless the new row matches the specified ...


9

It's to do with Python's object model - there's always a way to get a reference to objects that could be unsafe. See the rexec module documentation and the restricted execution chapter of the docs for some info on the problems, as well as: https://www.researchgate.net/publication/228612669_Controlling_access_to_resources_within_the_python_interpreter http:/...


9

The error message clearly describes the problem: "operator does not exist: inet ~~ unknown" So you should use the native operators for inet type. In your case it should be ... where ip_address << '192.168.43/24'::inet; where the value after the slash specifies the number of significant bits in the value (24 bits = 3 bytes = x.y.z.any). For example ...


8

PostgreSQL 9.6+ onwards, would start to see Parallel-Query finally coming to PostgreSQL. For e.g. Concepts like Parallel Scan / Parallel Join / Parallel Aggregates are now already baked in, with more to come soon. What's really exciting is that there are reports confirming near-linear speed-up in some cases, which is pretty impressive!


8

The query you have You could simplify your query using a WINDOW clause, but that's just shortening the syntax, not changing the query plan. SELECT id, trans_ref_no, amount, trans_date, entity_id , SUM(amount) OVER w AS trans_total , COUNT(*) OVER w AS trans_count FROM transactiondb WINDOW w AS (PARTITION BY entity_id, date_trunc('month',...



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