What would you recommend to do a quick test for this? (i.e WAL on
internal disk vs WALon the 12 disk raid array )?
On Thu, Jun 24, 2010 at 6:31 AM, Scott Marlowe wrote:
> On Thu, Jun 24, 2010 at 5:14 AM, Matthew Wakeling wrote:
>> On Wed, 23 Jun 2010, Scott Marlowe wrote:
We have a 12
I have a situation where we are limited by the chassis on the box (and cost).
We have a 12 x 600G hot swappable disk system (raid 10)
and 2 internal disk ( 2x 146G)
We would like to maximize storage on the large disks .
Does it make sense to put the WAL and OS on the internal disks and use
the
The combination index works great. Would adding the combination index
guarantee that the optimizer will choose that index for these kind of
queries involving the columns in the combination. I verified a couple
of times and it picked the right index. Just wanted to make sure it
does that consistentl
Appears to have helped with the combination index. I'll need to
eliminate caching effects before making sure its the right choice.
Thanks for the suggestion.
On Tue, Jun 22, 2010 at 7:01 PM, Tom Lane wrote:
> Alvaro Herrera writes:
>> Excerpts from Anj Adu's message of mar jun 22 17:44:39 -0400
I did post the explain analyze..can you please clarify
On Tue, Jun 22, 2010 at 6:10 PM, Joshua D. Drake wrote:
> On Tue, 2010-06-22 at 18:00 -0700, Anj Adu wrote:
>> i have several partitions like this (similar size ...similar data
>> distribution)..these partitions are only &q
i have several partitions like this (similar size ...similar data
distribution)..these partitions are only "inserted"..never updated.
Why would I need to vacuum..
I can reindex..just curious what can cause the index to go out of whack.
On Tue, Jun 22, 2010 at 4:44 PM, Alvaro Herrera
wrote:
> Exc
This query seems unreasonable slow on a well-indexed table (13 million
rows). Separate indexes are present on guardid_id , from_num and
targetprt columns.
The table was analyzed with a default stats target of 600.
Postgres 8.1.9 on 2 cpu quad core 5430 with 32G RAM (work_mem=502400)
6 x 450G 15K
Can you provide these details
work_mem
How much physical memory there is on your system
Most out of memory errors are associated with a high work_mem setting
On Sun, Jun 13, 2010 at 6:25 AM, AI Rumman wrote:
> Whenever I run this query, I get out of memory error:
>
>
> explain analyze
> select
an chosen will almost certainly be slower for any
> non-trivial query. You can put a 200mph speedometer in a
> VW bug but it will never go 200mph.
>
> Regards,
> Ken
>
> On Thu, Jun 10, 2010 at 07:54:01PM -0700, Anj Adu wrote:
>> I changed random_page_cost=4 (earlier 2) and th
I changed random_page_cost=4 (earlier 2) and the performance issue is gone
I am not clear why a page_cost of 2 on really fast disks would perform badly.
Thank you for all your help and time.
On Thu, Jun 10, 2010 at 8:32 AM, Anj Adu wrote:
> Attached
>
> Thank you
>
>
> On Th
you are right..the word "zone" was replaced by "area" (my bad )
everything else is as is.
Apologies for the confusion.
On Thu, Jun 10, 2010 at 9:42 AM, Robert Haas wrote:
> On Thu, Jun 10, 2010 at 11:32 AM, Anj Adu wrote:
>> Attached
>
> Hmm. Well, I&#x
Attached
Thank you
On Thu, Jun 10, 2010 at 6:28 AM, Robert Haas wrote:
> On Wed, Jun 9, 2010 at 11:17 PM, Anj Adu wrote:
>> The plan is unaltered . There is a separate index on theDate as well
>> as one on node_id
>>
>> I have not specifically disabled sequential
, Tom Lane wrote:
> Robert Haas writes:
>> On Thu, Jun 3, 2010 at 4:37 PM, Anj Adu wrote:
>>> Link to plan
>>>
>>> http://explain.depesz.com/s/kHa
>
>> Your problem is likely related to the line that's showing up in red:
>
>&
Thanks..I'll try this. Should I also rebuild the contrib modules..or
just the core postgres database?
On Sat, Jun 5, 2010 at 2:38 AM, Scott Marlowe wrote:
> On Fri, Jun 4, 2010 at 12:21 PM, Anj Adu wrote:
>> The behaviour is different in postgres 8.1.9 (much faster) (the table
&g
The behaviour is different in postgres 8.1.9 (much faster) (the table
has 9 million rows instead of 25 million..but the query comes back
very fast (8 seconds)..
Wonder if this is very specific to 8.4.0
On Fri, Jun 4, 2010 at 11:01 AM, Anj Adu wrote:
> Does the difference in expected and act
Does the difference in expected and actual rows as seen by the planner
a big factor? Even after an analyze...the results are similar. (there
is a big diff between expected and actual)
Partition has 25 million rows
On Fri, Jun 4, 2010 at 10:41 AM, Anj Adu wrote:
> 2010/6/4 :
>>> I
2010/6/4 :
>> I am reposting as my original query was mangled
>>
>> The link to the explain plan is here as it does not paste well into
>> the email body.
>>
>> http://explain.depesz.com/s/kHa
>>
>>
>> The machine is a 2 cpu quad core 5430 with 32G RAM and 6x450G 15K
>> single raid-10 array
>>
>>
I'm interested in why the two partitions dev4_act_dy_fact and
> dev4_act_dy_fact_2010_05_t3 are treated so differently. I'm guessing that
> the former is the parent and the latter the child table?
Yes..you are correct.
>
> When accessing the parent table, Postgres is able to use a bitmap AND inde
I am reposting as my original query was mangled
The link to the explain plan is here as it does not paste well into
the email body.
http://explain.depesz.com/s/kHa
The machine is a 2 cpu quad core 5430 with 32G RAM and 6x450G 15K
single raid-10 array
1G work_mem
default_statistics_target=1000
Link to plan
http://explain.depesz.com/s/kHa
On Thu, Jun 3, 2010 at 11:43 AM, Andy Colson wrote:
> On 6/3/2010 12:47 PM, Anj Adu wrote:
>>
>> I cant seem to pinpoint why this query is slow . No full table scans
>> are being done. The hash join is taking ma
I cant seem to pinpoint why this query is slow . No full table scans
are being done. The hash join is taking maximum time. The table
dev4_act_action has only 3 rows.
box is a 2 cpu quad core intel 5430 with 32G RAM... Postgres 8.4.0
1G work_mem
20G effective_cache
random_page_cost=1
default_statis
I have a 16G box and tmpfs is configured to use 8G for tmpfs .
Is a lot of memory being wasted that can be used for Postgres ? (I am
not seeing any performance issues, but I am not clear how Linux uses
the tmpfs and how Postgres would be affected by the reduction in
memory)
Sriram
--
Sent via p
If the partitioned column in your where clause does not use hardcoded
values ...e.g datecolumn between 'year1' and 'year2' ..the query
planner will check all partitions ..this is a known issue with the
optimizer
On Mon, Mar 8, 2010 at 10:28 AM, Konrad Garus wrote:
> Hello,
>
> I am evaluating a m
When I use intervals in my query e.g col1 between current_timestamp -
interval '10 days' and current_timestamp...the optimizer checks ALL
partitions whereas if I use col1 between 2 hardcoded dates..only
the applicable partitions are scanned.
--
Sent via pgsql-performance mailing list (pgsql-p
The recommended partitioning guideline is if your table exceeds 2G
Partitioning benefits:
1. Purging old data very quickly (this is one of the biggest
benefits...especially if you have to purge very often...dont even
think of using DELETE)
2. Performance for certain types of queries where full t
Why is reindex needed ?Unless most of the key values get deleted
frequently..this is not needed. (I am assuming postgres 8.x and above)
On Sun, Nov 8, 2009 at 7:58 PM, Robert Haas wrote:
> On Sat, Nov 7, 2009 at 11:58 PM, Aris Samad-Yahaya
> wrote:
>> We vacuum analyze nightly, and vacuum no
> I have an application wherein a process needs to read data from a stream and
> store the records for further analysis and reporting. The data in the stream
> is in the form of variable length records with clearly defined fields – so
> it can be stored in a database or in a file. The only caveat i
I would recommend (if at all possible) to partition the table and drop
the old partitions when not needed. This will guarantee the space
free-up without VACUUM overhead. Deletes will kill you at some point
and you dont want too much of the VACUUM IO overhead impacting your
performance.
On Mon, Nov
large tables.
On Fri, Oct 30, 2009 at 1:01 PM, Greg Stark wrote:
> On Fri, Oct 30, 2009 at 12:53 PM, Anj Adu wrote:
>> Any relational database worth its salt has partitioning for a reason.
>>
>> 1. Maintenance. You will need to delete data at some
>> point.(cleanup)...Par
Any relational database worth its salt has partitioning for a reason.
1. Maintenance. You will need to delete data at some
point.(cleanup)...Partitions are the only way to do it effectively.
2. Performance. Partitioning offer a way to query smaller slices of
data automatically (i.e the query opt
st=15.20..15.20 rows=520
width=122) (actual time=0.096..0.096 rows=85 loops=1)
-> Seq Scan on realdev_dddzn dzone
(cost=0.00..15.20 rows=520 width=122) (actual time=0.006..0.049
rows=85 loops=1)
-> Hash (cost=18.50..18.50 rows=850 width=62)
(a
Join did not help. A sequential scan is still being done. The
hardcoded value in the IN clause performs the best. The time
difference is more than an order of magnitude.
2009/10/29 Angayarkanni :
>
> 2009/10/29 Grzegorz Jaśkiewicz
>>
>>
>> On Wed, Oct 28, 2009 at
Postgres consistently does a sequential scan on the child partitions
for this query
select * from partitioned_table
where partitioned_column > current_timestamp - interval 8 days
where x in (select yy from z where colname like 'aaa%')
If I replace the query with
select * from partitioned_table
w
:
> Anj Adu writes:
>> This query is doing a sequential scan on the child partitions even
>> though indexes on all constrained columns are present
>
> It looks to me like it's doing exactly what it is supposed to, ie,
> indexscan on the partitions where it would help and
Hi
This query is doing a sequential scan on the child partitions even
though indexes on all constrained columns are present
The box is very lightly loaded (8 core 15K 6x300G Raid 10 disks)
explain analyze
select thedate,sent.theboxid_id,sub_box_id,box_num,sum(summcount) as
event_count,'ACC'
idual
partitions.
On Wed, Oct 14, 2009 at 11:02 PM, Anj Adu wrote:
> That..however is not how the data is distributed...the query is doing
> a sequential scan on "every" partition that is within the date
> constraint specified...i.e 2009-10-07 thru 2009-10-13..there is no
>
Hi
This query is doing a sequential scan on the child partitions even
though indexes on all constrained columns are present
The box is very lightly loaded (8 core 15K 6x300G Raid 10 disks)
explain analyze
select thedate,sent.theboxid_id,sub_box_id,box_num,sum(summcount) as
event_count,'ACC'
37 matches
Mail list logo
