Tag Info

Hot answers tagged

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


6

Assuming you checked off usual suspects in the wiki page as commented by @a_horse. Also see the spin-off addressing the discussion of bitmap index scans and the size of work_mem. "Recheck Cond:" line in query plans with a bitmap index scan Query This rewritten query should be substantially faster: SELECT id || ':' || group_number AS uniq_id ...


6

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


5

If you don't need the "optimal fill", you could use a window function and something like this: select * from ( select id, name, price, category, sum(price) over (order by price, date_created) as so_far from my_table where price > 40 and category = 'buy' ) t where so_far <= 12; If you are mixing "groups" of orders, you might ...


5

Is there any way I can speed this up? Yes. Don't use a varchar column for an integer number. Use integer or bigint if you burn that many IDs - much smaller in table and index and faster to process. Since you are ranking 10 million rows in your test, this is going to make a substantial difference. player_id VARCHAR(200) NOT NULL, player_id int NOT ...


5

Store timestamps as timestamp, or rather timestamptz (timestamp with time zone) since you are dealing with multiple time zones. That enforces valid data and is typically most efficient. Be sure to understand the data type, there are some misconceptions floating around: Time zone storage in PostgreSQL timestamps Ignoring timezones altogether in Rails and ...


5

14.04 shipped with PostgreSQL 9.3, so that's what you're going to get if you use Ubuntu's repositories. If you want PostgreSQL 9.4 or newer releases you need to add a 3rd party package repository, like the official PostgreSQL repositories from http://apt.postgresql.org/ . See the instructions there for details. (You should also probably read ...


5

Imagine you are out with a group of friends and the conversation turns to movies. Someone asks, "What did you think of 'The Three Musketeers'?" You respond, "Which one?" What additional information would you need to be absolutely certain you are both thinking of the same movie? The director's name? The production studio? The year it was released? One of the ...


4

Have you tried rewriting your NOT IN predicates as LEFT JOINs? SELECT t1.id || ':' || t1.group_number AS uniq_id FROM table_one t1 LEFT JOIN table_two t2 ON t1.id = t2.id AND t1.group_number = t2.group_number LEFT JOIN table_three t3 ON t1.id = t3.id AND t3.timestamp > NOW() - INTERVAL '30 days' AND t3.client_id > 0 WHERE t2.id IS ...


4

As @Chris commented correctly on the referenced question: a little investigation seems to indicate that the recheck condition is always printed in the EXPLAIN, but is actually only performed when work_mem is small enough that the bitmap becomes lossy. Thoughts? http://www.postgresql.org/message-id/464F3C5D.2000700@enterprisedb.com While this is ...


4

Your issue appears to be that you are applying the same WINDOW (named w) for both your COUNT(*) and your rank(). When you use a WINDOW which contains an ORDER BY clause, and you then apply certain aggregations such as SUM or COUNT, it applies the aggregation continuously across the ordering, which is why your COUNT and rank() are identical. If you modify ...


4

Another way - if your version is 9.3+ - is to use the LATERAL syntax: SELECT * FROM generate_series('2015-07-06', '2016-07-03', '7 day'::interval) AS d (dt) , LATERAL ( SELECT fa.*, dt.* FROM dim_time AS dt INNER JOIN f_agent AS fa ON fa.f_agent__dim_time_id = dt.dim_time__id WHERE dim_time__week_start_date = d.dt ...


4

The super quick fix will be to apply DISTINCT ON() to your query. DISTINCT ON Postgres allows the use of the DISTINCT qualifier, but allows it to be applied across a subset of your returned columns from your SELECT query. In this case, you could simply rewrite your query as SELECT DISTINCT ON(dim_time__week_start_date) dim_time__week_start_date, ...


4

DB design Simplify your design radically: CREATE TABLE dim_time ( dim_time__id bigserial NOT NULL PRIMARY KEY, -- assuming PK dim_time__date date NOT NULL, dim_time__guid uuid NOT NULL DEFAULT uuid_generate_v4(), ); These columns are functionally dependent and just noise bloating your table: dim_time__month_start_date date NOT NULL, ...


4

What you see is completely normal and expected. Note: Because smallserial, serial and bigserial are implemented using sequences, there may be "holes" or gaps in the sequence of values which appears in the column, even if no rows are ever deleted. A value allocated from the sequence is still "used up" even if a row containing that value is never ...


4

You should always store data in it's native datatype so you can use the built-in functions. And the data type of a timestamp is obviously a timestamp. Btw, a timestamp is not stored as a string, it's stored as an 8-byte integer, exactly the same as bigint: PostgreSQL documentation.


4

Your table definition looks reasonable all over now. With all columns NOT NULL the UNIQUE constraint will work as expected - except for typos and minor differences in spelling, which may be rather common I am afraid. Consider @a_horse's comment. Alternative with functional unique index The other option would be a functional unique index (similar to what ...


4

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


3

In addition to the solution of the simple case, add a PARTITION clause to the window functions in the inner query, to get group numbers per partition (per "timeline"). Combine group numbers with the respective timeline (id in your example) keep partitions separate in the second step: SELECT id, ts , min(foo) OVER (PARTITION BY id, foo_grp) AS foo ...


3

It seems you are after the percentile_disc() aggregate function. The documentation says the following about it: percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression) discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction The syntax is a bit strange for an aggreagte, ...


3

The pre-format solution used to be the good old concatenation, made safe by using quote_ident(). In your case, it would look like ... EXECUTE $$ CREATE TABLE IF NOT EXISTS $$ || quote_ident(rows.tablename) || $$ ( date date, number integer ) $$; ... Note that here I am using dollar quoting when building the ...


3

It is likely that this is not related to the path of the user. Centos 6 uses the alternatives system to control which version or implementation of various packages is active. In my system, if I do: $ which psql /usr/bin/psql $ ls -l /usr/bin/psql lrwxrwxrwx 1 root root 28 Nov 7 2013 /usr/bin/psql -> /etc/alternatives/pgsql-psql $ ls -l ...


3

The /128 in the address is the NUMBER OF BITS to match... not a range. So you would want 10.8.101.0/24 (match the first 24 bits). As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25 Please see the address subsection in the documentation


3

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


3

There is no feature built in to delete rows automatically on a time-based regime (that I would know of). You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose. Or you could use partitioning with weekly partitions that inherit from a master table, lets call it log. That would make deleting very cheap: ...


3

One way to do this is using window functions: SELECT DISTINCT type_id, book_id, title_id, page_id, first_value(lines) OVER wmin AS min_lines, first_value(created_at) OVER wmin AS min_created_at, first_value(lines) OVER wmax AS max_lines, first_value(created_at) OVER wmax AS max_created_at FROM tableX WINDOW wmin AS ...


3

SELECT id FROM accesses WHERE token IS NOT NULL; The perfect index for this specific query would be a partial index: CREATE INDEX accesses_foo_idx ON accesses(id) WHERE token IS NOT NULL; The index condition is the important part. On top of it, since you only retrieve id which is covered by the index, you can get index-only scans out of this (if the ...


3

While passing integer numbers, you can either cast the whole array: TG_ARGV::int[] Or you can cast an element, then it must be the element type: TG_ARGV[0]::int I used it that way in my answer to your previous question: SELECT in trigger function in two tables However, you are not passing integer numbers, but the text representation of integer ...


3

You could use an inner join to filter for rows that share a (p_id, k_id) value with rows that have a (t_id, pos) of (1,1) (sqlfiddle): select base.p_id , base.k_id , json_agg(base order by base.p_id, base.k_id, base.t_id, base.pos) as stats from ( select p_id , k_id , t_id , pos from ...


3

I was assuming that these new records will not be visible to the current transaction, but proved wrong on testing. That's not the case in the default READ COMMITTED isolation. Changes from committed transactions become visible at the start of the next statement in a transaction. Each statement still has a snapshot, so you can't have rows appear within a ...



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