Re: [GENERAL] Understanding "seq scans"

2015-10-13 Thread Lele Gaifax
Alvaro Herrera writes: > So 10% of your rows in the master_l10n table start with "quattro"? > That's pretty odd, isn't it? How did you manufacture these data? Well, not a real scenario for sure, but definitely not odd: I just needed an "extremely" big dataset to test out several different strat

Re: [GENERAL] Understanding "seq scans"

2015-10-13 Thread Lele Gaifax
Merlin Moncure writes: > Your data and indexes are organized such that an index is only marginally > helpful, or so the planner thinks. Yes, just wanted to get rid of that suspicious "seq scan", that was effectively caused by a bad index, as Alvaro pointed out. Thank you, ciao, lele. -- nickna

[GENERAL] hanged session on index creation

2015-10-13 Thread Mikhail
Hi, I'm running supposedly an easy operation - renaming index and my session hangs on it. pg_stat_activity shows that process is active and is waiting for lock: =# select application_name, waiting, state, query from pg_stat_activity where pid = 15179; application_name │ waiting │ state  │ query

[GENERAL] hanged session on index creation

2015-10-13 Thread Mikhail
Sorry for my previous message, it went to wrong thread. Creating this as a new one. I'm running supposedly an easy operation - renaming index and my session hangs on it. pg_stat_activity shows that process is active and is waiting for lock: =# select application_name, waiting, state, query from

Re: [GENERAL] hanged session on index creation

2015-10-13 Thread Tom Lane
=?UTF-8?B?TWlraGFpbA==?= writes: > I'm running supposedly an easy operation - renaming index and my session > hangs on it. pg_stat_activity shows that process is active and is waiting for > lock: > locktype ¦ relation ¦ relname ¦ virtualtransaction ¦ pid ¦ mode ¦ > granted ¦ fastpath Â

[GENERAL] Re[2]: [GENERAL] hanged session on index creation

2015-10-13 Thread Mikhail
Thanks for advice about queries, i guess you're talking about those from page  https://wiki.postgresql.org/wiki/Lock_Monitoring . Previously, I was using "blockedby" template from pgAdmin's "Server Status". And you are absolutely right suspecting distributed transactions. The most interesting

Re: [GENERAL] Understanding "seq scans"

2015-10-13 Thread Alvaro Herrera
Lele Gaifax wrote: > Alvaro Herrera writes: > > > So 10% of your rows in the master_l10n table start with "quattro"? > > That's pretty odd, isn't it? How did you manufacture these data? > > Well, not a real scenario for sure, but definitely not odd: I just needed an > "extremely" big dataset to

[GENERAL] ID column naming convention

2015-10-13 Thread droberts
Hi, is there a problem calling ID's different when used as a FK vs table ID? For example mydimtable () ID name description myfacttable () my_dim_id # FK to ID above total_sales I 'think' if I don't enforce foreign key constraints, then this practice prevents tools from being able to

Re: [GENERAL] ID column naming convention

2015-10-13 Thread Rob Sargent
On 10/13/2015 11:36 AM, droberts wrote: Hi, is there a problem calling ID's different when used as a FK vs table ID? For example mydimtable () ID name description myfacttable () my_dim_id # FK to ID above total_sales I 'think' if I don't enforce foreign key constraints, then t

Re: [GENERAL] Understanding "seq scans"

2015-10-13 Thread Lele Gaifax
Alvaro Herrera writes: > I imagined it would be something like that. It's not the most useful > set of test data, precisely because it doesn't accurately reflect what > you're going to have in practice. I suggest you enter some actual text, > even if it's just text from Don Camillo or whatever.

Re: [GENERAL] ID column naming convention

2015-10-13 Thread David G. Johnston
On Tue, Oct 13, 2015 at 1:36 PM, droberts wrote: > I 'think' if I don't enforce foreign key constraints, then this practice > prevents tools from being able to generate ERD diagrams right? > ​Yes, if you don't name them the same and don't setup an actual FK then there is no reason to assume that

Re: [GENERAL] Merge join vs merge semi join against primary key

2015-10-13 Thread Sean Rhea
All, No, the customers table is not 100% the same. This is a live production system, so the data is (unfortunately) changing under us a bit here. That said, there are still some strange things going on. I just reran everything. The query plan time hasn't changed, but as Jeremy, Igor, and David all

Re: [GENERAL] Merge join vs merge semi join against primary key

2015-10-13 Thread Tom Lane
Sean Rhea writes: > No, the customers table is not 100% the same. This is a live production > system, so the data is (unfortunately) changing under us a bit here. That > said, there are still some strange things going on. I just reran > everything. The query plan time hasn't changed, but as Jeremy

Re: [GENERAL] Merge join vs merge semi join against primary key

2015-10-13 Thread Sean Rhea
Tom, Just to clarify, is the lefthand input customers or balances? And turning off merge joins "fixes" everything, including the runtime: production=> set enable_mergejoin = off; SET production=> explain analyze SELECT ac.* FROM balances ac JOIN customers o ON (o.id = ac.customer_id AND o.group_

[GENERAL] FOSDEM 2016, 30th and 31st January 2016, Brussels: Call for Participation

2015-10-13 Thread Stefan Keller
Hi, The sixteenth edition of FOSDEM will take place on Saturday 30th and Sunday 31st January 2016 at the usual location, the ULB Campus Solbosch in Brussels. There's a upcoming deadline for first batch of main track proposals: 16 October 2015. And there's e.g. a proposal for a Geospatial devroomː

Re: [GENERAL] ID column naming convention

2015-10-13 Thread Gavin Flower
On 14/10/15 06:36, droberts wrote: Hi, is there a problem calling ID's different when used as a FK vs table ID? For example mydimtable () ID name description myfacttable () my_dim_id # FK to ID above total_sales I 'think' if I don't enforce foreign key constraints, then this p

Re: [GENERAL] Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

2015-10-13 Thread Adrian Klaver
On 10/12/2015 07:53 PM, Victor Blomqvist wrote: Do you have some advice how to design my functions to work around this problem? If I understand your conversation correct the problem is returning the rowtype users from the function. If so, I can think of two workarounds (both quite inconvenient a

[GENERAL] Archiving while idle every archive_timeout with wal_level hot_standby

2015-10-13 Thread Laurence Rowe
I'm seeing Postgres 9.4.4 archive while idle every archive_timeout when I set ``wal_level hot_standby``: $ initdb testpg > $ cat << 'EOF' >> testpg/postgresql.conf > > wal_level = hot_standby > > archive_mode = on > > archive_timeout = 10 > > checkpoint_timeout = 1h > > archive_command = 'echo $(d

Re: [GENERAL] ID column naming convention

2015-10-13 Thread droberts
Gavin Flower-2 wrote > On 14/10/15 06:36, droberts wrote: >> Hi, is there a problem calling ID's different when used as a FK vs table >> ID? >> For example >> >> >> mydimtable () >> ID >> name >> description >> >> >> myfacttable () >>my_dim_id # FK to ID above >>total_sales >> >> >>

Re: [GENERAL] ID column naming convention

2015-10-13 Thread John R Pierce
On 10/13/2015 3:27 PM, droberts wrote: Thanks. My only question is how do you create a schema diagram (ERD) then? The tool won't know what the relationships are unless maybe you put foreign key constraints on. BTW does anyone recommend a tool to to that? I've been playing with DbVisualizer.

[GENERAL] Creating Report for PieChart

2015-10-13 Thread Alex Magnum
Hello, I need to process some statistics for a pie chart (json) where I only want to show a max of 8 slices. If I have more data points like in below table I need to combine all to a slice called others. If there are less or equal 8 i use them as is. I am currently doing this with a plperl functio

Re: [GENERAL] Creating Report for PieChart

2015-10-13 Thread Bruce Momjian
On Wed, Oct 14, 2015 at 01:56:11AM +0200, Alex Magnum wrote: > Hello, > I need to process some statistics for a pie chart (json) where I only want to > show a max of 8 slices. If I have more data points like in below table I need > to combine all to a slice called others. If there are less or equal

Re: [GENERAL] Creating Report for PieChart

2015-10-13 Thread Scott Mead
> On Oct 13, 2015, at 19:56, Alex Magnum wrote: > > Hello, > I need to process some statistics for a pie chart (json) where I only want to > show a max of 8 slices. If I have more data points like in below table I need > to combine all to a slice called others. If there are less or equal 8 i

Re: [GENERAL] Creating Report for PieChart

2015-10-13 Thread Scott Mead
On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead wrote: > > > On Oct 13, 2015, at 19:56, Alex Magnum wrote: > > Hello, > I need to process some statistics for a pie chart (json) where I only want > to show a max of 8 slices. If I have more data points like in below table I > need to combine all to a

Re: [GENERAL] Creating Report for PieChart

2015-10-13 Thread dinesh kumar
+Adding to Scott On Tue, Oct 13, 2015 at 6:25 PM, Scott Mead wrote: > > > > On Tue, Oct 13, 2015 at 9:14 PM, Scott Mead wrote: > >> >> >> On Oct 13, 2015, at 19:56, Alex Magnum wrote: >> >> Hello, >> I need to process some statistics for a pie chart (json) where I only >> want to show a max of