Tag Info

Hot answers tagged

4

You have to first set expectations - a screen that does such and such activities should complete each action in 1 second and all actions in 5 seconds and so on. For example, a search screen should retrieve results in 3 seconds, the booking actions (ticket booking) should be completed in 30 seconds etc. Then work towards meeting those targets. That is the ...


4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


3

This would do what you desire: WITH p AS ( INSERT INTO parent_table (column_1) SELECT $1 RETURNING id) INSERT INTO child_table (parent_table_id, column_a, column_b) SELECT p.id, t.a, t.b FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t The subtle difference here is that unnest() calls in the same SELECT list are ...


2

Start with the manual page on Write Ahead Log wal_writer_delay (integer) Specifies the delay between activity rounds for the WAL writer. In each round the writer will flush WAL to disk. It then sleeps for wal_writer_delay milliseconds, and repeats. The default value is 200 milliseconds (200ms). Note that on many systems, the effective resolution of ...


2

An array representing the path from the root up to the leaf should achieve the desired sort order: WITH RECURSIVE node_rec AS ( (SELECT 1 AS depth, ARRAY[node] AS path, * FROM nodes WHERE parent IS NULL LIMIT 10 ) UNION ALL SELECT r.depth + 1, r.path || n.node, n.* FROM node_rec r JOIN nodes n ON n.parent = ...


2

You cannot use a parameter name (_t) as return type. Use polymorphic types instead: CREATE OR REPLACE FUNCTION vsr_versioning_at_time(_t anyelement, _d timestamp) RETURNS SETOF anyelement AS $func$ BEGIN RETURN QUERY EXECUTE format(' SELECT DISTINCT ON (gid) * FROM %s WHERE vrs_start_time <= $1 ORDER BY gid, ...


1

Use COALESCE to catch and replace NULL values: SELECT f.name AS foo , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes FROM foo f LEFT JOIN bar b ON b.fooid = f.id GROUP BY 1; concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with ...


1

The simplest method I have found to accomplish this is to swap out the string concatenation operator for the string concatenation function concat(). For some reason the former apparently coerces the entire result to NULL if one operand is null, as opposed to the latter which effectively casts any NULL arguments to ''. So this query performs as desired: ...


1

This page in the manual has detailed instructions how to deal with SHMMAX and SHMALL. It depends on your OS. For Linux (example from the manual, to allow 16 GB): $ sysctl -w kernel.shmmax=17179869184 $ sysctl -w kernel.shmall=4194304 Your number is very high: 52033798400 translates to 48 GB. Do you have that much RAM available? Also, more quotes: ...


1

For this query: select distinct date from mybigtable; or its twin: select date from mybigtable group by 1; ... the whole table has to be read. Postgres is not going to use any index, except, possibly, a covering index that is substantially smaller than the table itself. Postgres Wiki on slow counting. Also, to be precise, that's not a count. If you ...


1

Either you run an additional UPDATE (best in one transaction): UPDATE site_stats s SET total_users = 0 , monthly_users = 0 , weekly_users = 0 WHERE NOT EXISTS (SELECT 1 FROM users WHERE local_site = s.site_id) Or use this instead (I would do that): UPDATE site_stats s SET total_users = COALESCE(u.total, 0) , monthly_users = ...


1

I created your scenario with COLLATE = "C", and both queries use a bitmap index scan index on other_names_lower_trgm_gin as expected. SQL Fiddle with a table of ~ 10k rows, Postgres 9.2.4, COLLATE = "C". There is probably something wrong in your setup that is not in your question. Run (takes some time for big tables and an exclusive lock!): VACUUM FULL ...


1

You've left out so much that your chances of recovering useful data are negligible. pg_clog contains the commit/rollback logs. Without these, the system doesn't know which parts of the database files are valid and which are not. (gross oversimplification, but hey). pg_xlog, the write-ahead logs. Without these, the database can't handle incomplete writes, ...


1

I suggest you to check out MindArray IPM. It provides much deeper insight of Postgresql performance by rendering you a customizable performance metrics for monitoring key performance indicators. All the performance issues related to slow SQL, table locks and user sessions are reported to DBA/IT admins in real-time. IPM allows you to specify your own ...



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