Re: [GENERAL] Addled index

2013-03-18 Thread Oleg Alexeev
On 17 March 2013 08:30, Tom Lane wrote: > Oleg Alexeev writes: > > * it is varchar columns, 256 and 32 symbols length > > * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 > > * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, > > commit_delay = 100, commit_sib

Re: [GENERAL] Addled index

2013-03-18 Thread Greg Jaskiewicz
On 17 Mar 2013, at 04:30, Tom Lane wrote: > Oleg Alexeev writes: >> * it is varchar columns, 256 and 32 symbols length >> * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 >> * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, >> commit_delay = 100, commit_sibl

Re: [GENERAL] Addled index

2013-03-16 Thread Tom Lane
Oleg Alexeev writes: > * it is varchar columns, 256 and 32 symbols length > * encoding, collation and ctype: UTF8, en_US.utf8, en_US.utf8 > * autovacuum, fsync off, full_page_writes = on, wal_writer_delay = 500ms, > commit_delay = 100, commit_siblings = 10, checkpoint_timeout = 20min, > checkpoint

Re: [GENERAL] Addled index

2013-03-16 Thread Oleg Alexeev
On 16 March 2013 19:10, Tom Lane wrote: > Alban Hertroys writes: > > If there's actually something wrong with the database; it looks a bit > like your tables and your indexes get out of sync somehow, which normally > wouldn't be possible. I'm mostly guessing, but perhaps one of the below has > s

Re: [GENERAL] Addled index

2013-03-16 Thread Oleg Alexeev
On 16 March 2013 14:32, Alban Hertroys wrote: > On Mar 16, 2013, at 9:33, Oleg Alexeev wrote: > > On 16 March 2013 01:21, Steve Crawford wrote: > >> On 03/15/2013 11:29 AM, Oleg Alexeev wrote: >> >>> We've faced with strange index problem. >>> >>> At some moment index became bad and queries does

Re: [GENERAL] Addled index

2013-03-16 Thread Tom Lane
Alban Hertroys writes: > If there's actually something wrong with the database; it looks a bit like > your tables and your indexes get out of sync somehow, which normally wouldn't > be possible. I'm mostly guessing, but perhaps one of the below has something > to do with it: > Maybe you turned

Re: [GENERAL] Addled index

2013-03-16 Thread Alban Hertroys
On Mar 16, 2013, at 9:33, Oleg Alexeev wrote: > On 16 March 2013 01:21, Steve Crawford wrote: > On 03/15/2013 11:29 AM, Oleg Alexeev wrote: > We've faced with strange index problem. > > At some moment index became bad and queries does not return any data. > > For example, there are two tables

Re: [GENERAL] Addled index

2013-03-16 Thread Oleg Alexeev
On 16 March 2013 01:21, Steve Crawford wrote: > On 03/15/2013 11:29 AM, Oleg Alexeev wrote: > >> We've faced with strange index problem. >> >> At some moment index became bad and queries does not return any data. >> >> For example, there are two tables - A (id, name) and B (id, name, a_id). >> B.a

Re: [GENERAL] Addled index

2013-03-15 Thread Steve Crawford
On 03/15/2013 11:29 AM, Oleg Alexeev wrote: We've faced with strange index problem. At some moment index became bad and queries does not return any data. For example, there are two tables - A (id, name) and B (id, name, a_id). B.a_id is foreign key to A. Both name columns in tables contains i

[GENERAL] Addled index

2013-03-15 Thread Oleg Alexeev
We've faced with strange index problem. At some moment index became bad and queries does not return any data. For example, there are two tables - A (id, name) and B (id, name, a_id). B.a_id is foreign key to A. Both name columns in tables contains identical values for A.id = B.a_id. A.name column