Optimizer choosing the wrong plan

2018-11-26 Thread Viswanath
*Postgres server version - 9.5.10* *RAM - 128 GB* *WorkMem 64 MB* *Problematic query with explain :* *Query 1 (original):* explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON myTable1.ID=myTable2.ID WHERE myTable1.bool_val = true) AND (myTable1.small_intval IN (1,2,3))) AN

autovacuum run but last_autovacuum is empty

2018-11-26 Thread Mariel Cherkassky
Hi, I checked pg_stat_all_tables and I see that the last_autovacuum is empty for all the tables but in the database`s log I see that autovacuum was running and deleted records(not all of them but still deleted...). Can someone explain why the pg_stat_all_tables doesnt show the real data ?

Re: autovacuum run but last_autovacuum is empty

2018-11-26 Thread Tom Lane
Mariel Cherkassky writes: > I checked pg_stat_all_tables and I see that the last_autovacuum is empty > for all the tables but in the database`s log I see that autovacuum was > running and deleted records(not all of them but still deleted...). > Can someone explain why the pg_stat_all_tables doesnt

Re: dsa_allocate() faliure

2018-11-26 Thread Jakub Glapa
So, the issue occurs only on production db an right now I cannot reproduce it. I had a look at dmesg and indeed I see something like: -- regards, pozdrawiam, Jakub Glapa On Fri, Nov 23, 2018 at 5:10 PM Justin Pryzby wrote: > On Fri, Nov 23, 2018 at 03:31:41PM +0100, Jakub Glapa wrote: > > Hi

Re: dsa_allocate() faliure

2018-11-26 Thread Jakub Glapa
sorry, the message was sent out to early. So, the issue occurs only on production db an right now I cannot reproduce it. I had a look at dmesg and indeed I see something like: postgres[30667]: segfault at 0 ip 557834264b16 sp 7ffc2ce1e030 error 4 in postgres[557833db7000+6d5000] and AFAI

Re: autovacuum run but last_autovacuum is empty

2018-11-26 Thread Mariel Cherkassky
I think that something is wrong because some tables has a value in last_autoanalyze and some dont while most of them were analyzed. For example : relname | n_live_tup | n_dead_tup | last_autovacuum| last_autoanalyze -++

Re: dsa_allocate() faliure

2018-11-26 Thread Justin Pryzby
Hi, thanks for following through. On Mon, Nov 26, 2018 at 04:38:35PM +0100, Jakub Glapa wrote: > I had a look at dmesg and indeed I see something like: > > postgres[30667]: segfault at 0 ip 557834264b16 sp 7ffc2ce1e030 > error 4 in postgres[557833db7000+6d5000] That's useful, I think "at

Re: Optimizer choosing the wrong plan

2018-11-26 Thread Jeff Janes
On Mon, Nov 26, 2018 at 5:11 AM Viswanath wrote: > *Postgres server version - 9.5.10* > *RAM - 128 GB* > *WorkMem 64 MB* > > *Problematic query with explain :* > *Query 1 (original):* > explain analyse SELECT myTable1.ID FROM myTable1 LEFT JOIN myTable2 ON > myTable1.ID=myTable2.ID WHERE my

Re: dsa_allocate() faliure

2018-11-26 Thread Jakub Glapa
Justin thanks for the information! I'm running Ubuntu 16.04. I'll try to prepare for the next crash. Couldn't find anything this time. -- regards, Jakub Glapa On Mon, Nov 26, 2018 at 4:52 PM Justin Pryzby wrote: > Hi, thanks for following through. > > On Mon, Nov 26, 2018 at 04:38:35PM +0100,

Re: autovacuum run but last_autovacuum is empty

2018-11-26 Thread Tom Lane
Mariel Cherkassky writes: > I think that something is wrong because some tables has a value in > last_autoanalyze and some dont while most of them were analyzed. It's possible that autovacuum abandoned some vacuum attempts without getting to the end because of lock conflicts.

Re: dsa_allocate() faliure

2018-11-26 Thread Alvaro Herrera
On 2018-Nov-26, Jakub Glapa wrote: > Justin thanks for the information! > I'm running Ubuntu 16.04. > I'll try to prepare for the next crash. > Couldn't find anything this time. As I recall, the appport stuff in Ubuntu is terrible ... I've seen it take 40 minutes to write the crash dump to disk,