On Thursday, December 20, 2012, Richard Neill wrote:
> Dear Tom,
>
> Thanks againg for your help on this.
>
> On 20/12/12 03:06, Tom Lane wrote:
>
>> Richard Neill writes:
>>
>>> The problem is, when I now run my query, the planner ignores the
>>> dedicated index "tbl_tracker_performance_1_**idx"
On Thursday, December 20, 2012, Richard Neill wrote:
>
>
>
> - What I'm trying to do is trace the history of the books
> through the system and assign each one a proper unique id.
> So, if I see a book with "parcel_id_code = 37",
> is it a new book (after pid wrap), or is it the same book I
On Thursday, December 20, 2012, Tom Lane wrote:
> Jeff Janes > writes:
> > In any case, I can't get it to prefer the full index in 9.1.6 at all.
> The
> > partial index wins hands down unless the table is physically clustered by
> > the parcel_id_code column. In which that case, the partial inde
On Thursday, December 20, 2012, Charles Gomes wrote:
> Jeff,
>
> The 8288 writes are fine, as the array has a BBU, it's fine. You see about
> 4% of the utilization.
>
BBU is great for latency, but it doesn't do much for throughput, unless it
is doing write combining behind the scenes. Is it HDD
On 21/12/12 02:34, Richard Neill wrote:
Reindexing only takes a couple of seconds, and restores correctness.
Interestingly, the partial index (after reindexing) is only 16kB in
size; whereas the table is 1.1 GB, and the normal single-column indexes
are about 250MB in size.
In terms of w
Dear Tom,
Thanks againg for your help on this.
On 20/12/12 03:06, Tom Lane wrote:
Richard Neill writes:
The problem is, when I now run my query, the planner ignores the
dedicated index "tbl_tracker_performance_1_idx", and instead uses both
of the full indexes... resulting in a much much slowe
On Thu, Dec 20, 2012 at 4:39 PM, Tom Lane wrote:
> Charles Gomes writes:
>> Using rules would be totally bad as I'm partitioning daily and after one
>> year having 365 lines of IF won't be fun to maintain.
>
> You should probably rethink that plan anyway. The existing support for
> partitioning
Charles Gomes writes:
> Using rules would be totally bad as I'm partitioning daily and after one year
> having 365 lines of IF won't be fun to maintain.
You should probably rethink that plan anyway. The existing support for
partitioning is not meant to support hundreds of partitions; you're
goi
True, that's the same I feel, I will be looking to translate the trigger to C
if I can find good examples, that should accelerate.
Using rules would be totally bad as I'm partitioning daily and after one year
having 365 lines of IF won't be fun to maintain.
-
Jeff,
The 8288 writes are fine, as the array has a BBU, it's fine. You see about 4%
of the utilization.
To target directly instead of doing :
INSERT INTO TABLE VALUES ()
I use:
INSERT INTO TABLE_PARTITION_01 VALUES()
By targeting it I see a huge performance increase.
I haven't tested using 1B
Hi,
On 21 December 2012 04:29, Charles Gomes wrote:
> When I target the MASTER table on all the inserts and let
> the trigger decide what partition to choose from it takes 4 hours.
>
> If I target the partitioned table directly during the
> insert I can get 4 times better performance. It takes 1
Yes, I'm doing multiple threads inserting to the same tables.
I don't think the WAL is the issue as I even tried going ASYNC (non acid),
disabled sync after writes, however still didn't got able to push full
performance.
I've checked the locks and I see lots of ExclusiveLock's with:
select * fr
On Thu, Dec 20, 2012 at 9:29 AM, Charles Gomes wrote:
> Hello guys
>
>
>
> I’m doing 1.2 Billion inserts into a table partitioned in
> 15.
>
>
>
> When I target the MASTER table on all the inserts and let
> the trigger decide what partition to choose from it takes 4 hours.
>
> If I target the part
On 21 December 2012 01:06, Kevin Grittner wrote:
> Huan Ruan wrote:
> > Kevin Grittner wrote:
>
> >> Frankly, at 12 microseconds per matched pair of rows, I think
> >> you're doing OK.
> >
> > This plan is the good one, I want the indexscan nested loop join and this
> > is only achieved after mak
Without hyperthreading CPU still not a bottleneck, while I/O is only 10%
utilization.
top - 14:55:01 up 27 min, 2 users, load average: 0.17, 0.19, 0.14
Tasks: 614 total, 17 running, 597 sleeping, 0 stopped, 0 zombie
Cpu(s): 73.8%us, 4.3%sy, 0.0%ni, 21.6%id, 0.1%wa, 0.0%hi, 0.1%si, 0.
Charles,
* Charles Gomes (charle...@outlook.com) wrote:
> I’m doing 1.2 Billion inserts into a table partitioned in
> 15.
Do you end up having multiple threads writing to the same, underlying,
tables..? If so, I've seen that problem before. Look at pg_locks while
things are running and see if t
None of the cores went to 100%. Looking at top during the inserts I can see
several cores working, but never more than 60% busy. The machine has 8 cores
(16 in HT).
The load is spread through the cores, didn't have a single maxed out. However
with HT on, technically it is overloaded.
top - 13
On Thu, Dec 20, 2012 at 10:29 AM, Charles Gomes wrote:
> Hello guys
>
> I’m doing 1.2 Billion inserts into a table partitioned in
> 15.
>
> When I target the MASTER table on all the inserts and let
> the trigger decide what partition to choose from it takes 4 hours.
>
> If I target the partitioned
Hello guys
I’m doing 1.2 Billion inserts into a table partitioned in
15.
When I target the MASTER table on all the inserts and let
the trigger decide what partition to choose from it takes 4 hours.
If I target the partitioned table directly during the
insert I can get 4 times better perfor
Jeff Janes writes:
> In any case, I can't get it to prefer the full index in 9.1.6 at all. The
> partial index wins hands down unless the table is physically clustered by
> the parcel_id_code column. In which that case, the partial index wins by
> only a little bit.
> This is what I did for the
Richard Neill writes:
> Also, I wonder whether it matters which order the indexes are created in?
IIRC, if the estimated costs of using two different indexes come out the
same (to within 1% or so), then the planner keeps the first-generated
path, which will result in preferring the index with sma
Huan Ruan wrote:
> Kevin Grittner wrote:
>> Frankly, at 12 microseconds per matched pair of rows, I think
>> you're doing OK.
>
> This plan is the good one, I want the indexscan nested loop join and this
> is only achieved after making all these costing factors change. Before
> that, it was hash j
22 matches
Mail list logo
