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


19

Update: Tested all 5 queries in SQLfiddle with 100K rows (and 2 separate cases, one with few (25) distinct values and another with lots (around 25K values). A very simple query would be to use UNION DISTINCT. I think it would be most efficient if there is a separate index on each of the four columns It would be efficient with a separate index on each of the ...


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


12

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

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


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


11

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


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


11

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the ...


11

Use PostgreSQL's built-in uuid data type, and create a regular b-tree index on it. There is no need to do anything special. This will result in an optimal index, and will also store the uuid field in as compact a form as is currently practical. (Hash indexes in PostgreSQL are not crash-safe and are really a historical relic that tend to perform no better ...


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


11

In addition to what @Craig provided (and correcting some of it): Effective Postgres 9.4, UNIQUE, PRIMARY KEY and EXCLUDE constraints are checked immediately after each row when defined NOT DEFERRABLE. This is different from other kinds of NOT DEFERRABLE constraints (currently only REFERENCES (foreign key)) which are checked after each statement. We worked ...


10

In the absence of any answers I've explored the issue further myself. It looks like user-defined functions can handle all base types, including bytea and smallint[], so this doesn't affect the choice of representation much. I tried out several different representations on a PostgreSQL 9.4 server running locally on a Windows 7 laptop with a vanilla ...


10

As I understand it, your issue here is that the constraint is checked after each statement, but you want it checked at the end of the transaction, so it compares the before-state to the after-state, ignoring the intermediate states. If so, that is possible with a deferrable constraint. See SET CONSTRAINTS and DEFERRABLE constraints as documented in CREATE ...


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


10

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

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

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


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


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

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


9

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


9

To be clear, I'd use union as ypercube suggests, but it is also possible with arrays: select distinct unnest( array_agg(distinct a)|| array_agg(distinct b)|| array_agg(distinct c)|| array_agg(distinct d) ) from t order by 1; SQLFiddle here


9

You could use LATERAL, like in this query: SELECT DISTINCT x.n FROM atable CROSS JOIN LATERAL ( VALUES (a), (b), (c), (d) ) AS x (n) ; The LATERAL keyword allows the right side of the join to reference objects from the left side. In this case, the right side is a VALUES constructor that builds a single-column subset out of the column values ...


9

Assumptions / Clarifications No need to differentiate between infinity and open upper bound (upper(range) IS NULL). (You can have it either way, but it's simpler this way.) NULL vs. infinity in PostgreSQL range types Since date is a discrete type, all ranges have default [) bounds. Per documentation: The built-in range types int4range, int8range, ...



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