Re: [GENERAL] How to get good performance for very large lists/sets?

On 06 Oct 2014, at 10:02, Richard Frith-Macdonald wrote: > I'm wondering if anyone can help with advice on how to manage large > lists/sets of items in a postgresql database. > > I have a database which uses multiple lists of items roughly like this: > > CREATE TABLE List ( > ID SERIAL, >

Re: [GENERAL] Really strange foreign key constraint problem blocking delete

What should I disable? Corrupt index sounds like a possible case, but how do I fix this? EXPLAIN ANALYZE select * from cubesocialnetwork.tmp_autenticazionesocial where idautenticazionesocial = 1622 "Index Scan using tmpautenticazione on tmp_autenticazionesocial (cost=0.00..8.27 rows=1 width=53

Re: [GENERAL] Converting char to varchar automatically

Melvin Davidson writes: > Also, don't forget to test for relkind = 'r'. My bad from before. In principle you need to ignore attisdropped columns as well. Thinking about Jim's point about speed: it'd be wise to collapse any updates for multiple columns in the same table into one ALTER command, so

Re: [GENERAL] Converting char to varchar automatically

Jim Nasby writes: > Just a heads-up: each of those ALTER's will rewrite the table, so unless your > database is tiny this will be a slow process. There's ways to work around > that, but they're significantly more complicated. I think he's trying to get rid of all the blank-padding he's got righ

Re: [GENERAL] table versioning approach (not auditing)

On 07/10/14 10:47, Jim Nasby wrote: On 10/2/14, 9:27 AM, Adam Brusselback wrote: i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit[https://github.com/fxku/audit][https://github.com/fxku/audit[https://github.com/fxku

Re: [GENERAL] faster way to calculate top "tags" for a "resource" based on a column

On Oct 6, 2014, at 5:56 PM, Jim Nasby wrote: > Don't join to the resource table; there's no reason to because you're not > pulling anything from it. Thanks the reply! I'm not pulling anything from the resource table, but the join is necessary because I'm filtering based on it. ( see the WHERE

Re: [GENERAL] Converting char to varchar automatically

Also, don't forget to test for relkind = 'r'. My bad from before. Revised query is below. SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;' FROM pg_class c JOIN pg_nam

Re: [GENERAL] How to get good performance for very large lists/sets?

On 10/6/14, 3:02 AM, Richard Frith-Macdonald wrote: I'm wondering if anyone can help with advice on how to manage large lists/sets of items in a postgresql database. I have a database which uses multiple lists of items roughly like this: CREATE TABLE List ( ID SERIAL, Name VARCHAR

Re: [GENERAL] Converting char to varchar automatically

On 10/6/14, 12:41 PM, hari.fu...@gmail.com wrote: Melvin Davidson writes: This query might work for you, but double check all result statements first. SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TY

Re: [GENERAL] Really strange foreign key constraint problem blocking delete

On 10/5/14, 10:06 AM, Andy Colson wrote: On 10/05/2014 10:00 AM, Adrian Klaver wrote: On 10/05/2014 07:37 AM, Tim Mickelson wrote: Sorry about that, the precise version is: "PostgreSQL 9.1.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-3), 64-bit" Well 9.1

Re: [GENERAL] question

On 10/6/14, 10:15 AM, Andy Colson wrote: On 10/6/2014 10:07 AM, Emi Lu wrote: Hello List, May I know will cause any potential performance issues for psql8.3 please? version (PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu, compiled by GCC 4.1.2) FYI, 8.3 is almost 7 years old and is no longer s

Re: [GENERAL] Processor usage/tuning question

On 10/3/14, 2:24 PM, Israel Brewster wrote: I have a Postgresql 9.3.5 server running on CentOS 6.5. In looking at some stats today, I saw that it was handling about 4-5 transactions/second (according to the SELECT sum(xact_commit+xact_rollback) FROM pg_stat_database; query), and an instance of

Re: [GENERAL] faster way to calculate top "tags" for a "resource" based on a column

On 10/3/14, 11:21 AM, Jonathan Vanasco wrote: I've been able to fix most of my slow queries into something more acceptable, but I haven't been able to shave any time off this one. I'm hoping someone has another strategy. I have 2 tables: resource resource_2_tag I want to calc

Re: [GENERAL] table versioning approach (not auditing)

On 10/2/14, 9:27 AM, Adam Brusselback wrote: i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit[https://github.com/fxku/audit][https://github.com/fxku/audit[https://github.com/fxku/audit]]

Re: [GENERAL] How to get good performance for very large lists/sets?

Ohhh.. Um, completely left field, but, if your items are sequential in some way, maybe there is some gross misuse of ranges you could use? http://www.postgresql.org/docs/9.2/static/rangetypes.html -Andy Another thought, for the case of "find all the items in list A but not list B thin

Re: [GENERAL] How to get good performance for very large lists/sets?

On Mon, Oct 6, 2014 at 1:02 AM, Richard Frith-Macdonald < richard.frith-macdon...@brainstorm.co.uk> wrote: > I'm wondering if anyone can help with advice on how to manage large > lists/sets of items in a postgresql database. > > I have a database which uses multiple lists of items roughly like th

Re: [GENERAL] How to get good performance for very large lists/sets?

On 10/6/2014 12:52 PM, Richard Frith-Macdonald wrote: On 6 Oct 2014, at 17:54, Igor Neyman wrote: -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Frith-Macdonald Sent: Monday, October 06, 2014 4:02 AM To: pg

Re: [GENERAL] How to get good performance for very large lists/sets?

On 6 Oct 2014, at 17:54, Igor Neyman wrote: > >> -Original Message- >> From: pgsql-general-ow...@postgresql.org >> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard >> Frith-Macdonald >> Sent: Monday, October 06, 2014 4:02 AM >> To: pgsql-general@postgresql.org >> Subject

Re: [GENERAL] How to get good performance for very large lists/sets?

-Original Message- From: Richard Frith-Macdonald [mailto:richard.frith-macdon...@brainstorm.co.uk] Sent: Monday, October 06, 2014 1:53 PM To: Igor Neyman Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to get good performance for very large lists/sets? On 6 Oct 2014, at 17:5

Re: [GENERAL] How to get good performance for very large lists/sets?

Le 2014-10-06 à 13:22, Andy Colson a écrit : > On 10/6/2014 3:02 AM, Richard Frith-Macdonald wrote: >> I'm wondering if anyone can help with advice on how to manage large >> lists/sets of items in a postgresql database. >> >> I have a database which uses multiple lists of items roughly like t

Re: [GENERAL] Converting char to varchar automatically

Melvin Davidson writes: > This query might work for you, but double check all result statements first. > > SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || > quote_ident(c.relname) >|| ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;' > FROM pg_class c > JOIN pg_

Re: [GENERAL] How to get good performance for very large lists/sets?

On 10/6/2014 3:02 AM, Richard Frith-Macdonald wrote: I'm wondering if anyone can help with advice on how to manage large lists/sets of items in a postgresql database. I have a database which uses multiple lists of items roughly like this: CREATE TABLE List ( ID SERIAL, Name VARCHAR

Re: [GENERAL] Converting char to varchar automatically

Hi! >SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || >quote_ident(c.relname) > || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;' > FROM pg_class c > JOIN pg_namespace n ON n.oid = c.relnamespace > JOIN pg_attribute a ON a.attrelid = c.oid > JOIN pg_type t ON t

Re: [GENERAL] How to get good performance for very large lists/sets?

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Richard Frith-Macdonald Sent: Monday, October 06, 2014 4:02 AM To: pgsql-general@postgresql.org Subject: [GENERAL] How to get good performance for very large lists/sets? I'

Re: [GENERAL] How does PostgerSQL planner decide driving table

El 06/10/14 02:38, Shingo horiuchi escribió: > > Dear All, > > > > I have read about query tuning and attempt to check the impact of > > exchanging the driving table in Join condition. > > > > To test Simple Join condition, I prepared Two tables. > > One is the table for employees and another

[GENERAL] How to get good performance for very large lists/sets?

I'm wondering if anyone can help with advice on how to manage large lists/sets of items in a postgresql database. I have a database which uses multiple lists of items roughly like this: CREATE TABLE List ( ID SERIAL, Name VARCHAR ); and a table containing individual entries in the lis

Re: [GENERAL] Really strange foreign key constraint problem blocking delete

The administors (that are not from my company) are strongly against changing the Postgresql version :( so if this is a bug from Postgresql they want me to show a documentation that guarantees them that it will be fixed on an upgrade. On 05/10/2014 17:06, Andy Colson wrote: > On 10/05/2014 10:00 A

Re: [GENERAL] Converting char to varchar automatically

This query might work for you, but double check all result statements first. SELECT 'ALTER TABLE ' || quote_ident(n.nspname) || '.' || quote_ident(c.relname) || ' ALTER COLUMN ' || quote_ident(a.attname) || ' TYPE varchar;' FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace

Re: [GENERAL] question

On 10/6/2014 10:07 AM, Emi Lu wrote: Hello List, May I know will cause any potential performance issues for psql8.3 please? version (PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu, compiled by GCC 4.1.2) E.g., got 10 idle connections for 10 days. select current_query from pg_stat_activity wh

Re: [GENERAL] How to find greatest record before known values fast

On Sat, Oct 4, 2014 at 3:46 PM, Andrus wrote: > In my db people often looks for different period sales using different > filters and will sum > There are lot of sales and every sale is individual record in sales table. > So increasing sequential scan speed is important. > > I tried > > create tabl

[GENERAL] question

Hello List, May I know will cause any potential performance issues for psql8.3 please? version (PostgreSQL 8.3.18 on x86_64-unknown-linux-gnu, compiled by GCC 4.1.2) E.g., got 10 idle connections for 10 days.  select current_query from

Re: [GENERAL] Converting char to varchar automatically

On 10/6/2014 5:29 AM, Andrus wrote: Database contains about 300 tables. Most of them contain columns of char(n) type. How to convert all those columns to varchar automatically ? Is it possible to run some update commands in system tables for this ? Or is it possible to create pgsql script which c

[GENERAL] Converting char to varchar automatically

Database contains about 300 tables. Most of them contain columns of char(n) type. How to convert all those columns to varchar automatically ? Is it possible to run some update commands in system tables for this ? Or is it possible to create pgsql script which creates dynamically alter table alte