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


15

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

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


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

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


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

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

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

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


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


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

A LANGUAGE sql function must be a complete SQL statement (or more than one). Yours is just an expression. Just prepend SELECT, like you would when running it stand-alone. $BODY$ SELECT (SELECT ... The reason it complains at the + is that, surprisingly, it's legal to parenthesise a top-level query. This, for example, is a valid query: regress=> ...


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


9

TL;DR: New users can create tables in the public schema because people complained that it was too hard when they couldn't. If you dislike the defaults, you should probably create a new template database with the initial configuration that you want. For example, you might: DROP SCHEMA public; or REVOKE ALL ON SCHEMA public FROM public; GRANT USAGE ON ...


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

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


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

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


9

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


9

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


8

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


8

Aaron, In my recent work, I've been looking into some similar questions with PostgreSQL. PostgreSQL is almost always pretty good at generating the right query plan, but it isn't always perfect. Some simple suggestions would be to make sure to run an ANALYZE on your progresses table to make sure that you have updated statistics, but this isn't guaranteed ...



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