Re: [PERFORM] Estimates on partial index

K te er Xi Sent from Nine From: Jim Nasby Sent: 19-Aug-2016 03:32 To: Jeff Janes; Victor Yegorov Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Estimates on partial index On 8/18/16 3:06 PM, Jeff Janes wrote: > If you can

[PERFORM] Re: Big data INSERT optimization - ExclusiveLock on extension of the table

> 1. rename table t01 to t02 OK... > 2. insert into t02 1M rows in chunks for about 100k Why not just insert into t01?? Because of cpu utilization, it speeds up when load is divided > 3. from t01 (previously loaded table) insert data through stored procedure But you renamed t01 so it no longer

Re: [PERFORM] Estimates on partial index

On 8/18/16 3:06 PM, Jeff Janes wrote: If you can come up with a data generator which creates data that others can use to reproduce this situation, we can then investigate it in more detail. BTW, the easy fix to this is most likely to create an index on due_date WHERE is_current. Or perhaps par

Re: [PERFORM] Big data INSERT optimization - ExclusiveLock on extension of the table

On 8/17/16 6:45 AM, pinker wrote: 1. rename table t01 to t02 OK... 2. insert into t02 1M rows in chunks for about 100k Why not just insert into t01?? 3. from t01 (previously loaded table) insert data through stored procedure But you renamed t01 so it no longer exists??? to b01 - this happen

Re: [PERFORM] Estimates on partial index

On Thu, Aug 18, 2016 at 11:55 AM, Victor Yegorov wrote: > 2016-08-18 18:59 GMT+03:00 Jeff Janes : >> >> Both plans touch the same pages. The index scan just touches some of >> those pages over and over again. A large setting of >> effective_cache_size would tell it that the page will most likely

Re: [PERFORM] Estimates on partial index

2016-08-18 18:59 GMT+03:00 Jeff Janes : > Both plans touch the same pages. The index scan just touches some of > those pages over and over again. A large setting of > effective_cache_size would tell it that the page will most likely > still be in cache when it comes back to touch it again, meani

Re: [PERFORM] Estimates on partial index

2016-08-18 16:56 GMT+03:00 Tom Lane : > In that case you've got random_page_cost too far down. Values less than > the default of 4 are generally only appropriate if the bulk of your > database stays in RAM. > Oh, that's interesting. I was under impression, that r_p_c reflects IO speed, like — ma

Re: [PERFORM] Estimates on partial index

On Thu, Aug 18, 2016 at 6:52 AM, Victor Yegorov wrote: > Greetings. > > I have a question on why planner chooses `IndexScan` for the following > query: > > SELECT la.loan_id, la.due_date, la.is_current > FROM loan_agreements la WHERE la.is_current AND '2016-08-11' > > la.due_date; > ...

Re: [PERFORM] Estimates on partial index

Victor Yegorov writes: > Settings: > random_page_cost 2.5 ¤ > seq_page_cost1¤ > Question is — why IndexScan over partial index is estimated less than > BitmapHeap + BitmapIndex scan. And how can I tell Planner, that IndexScan > over 1/3 of table is not a good

[PERFORM] Estimates on partial index

Greetings. I have a question on why planner chooses `IndexScan` for the following query: SELECT la.loan_id, la.due_date, la.is_current FROM loan_agreements la WHERE la.is_current AND '2016-08-11' > la.due_date; Relevant (cannot post it all, sorry) table definition is: