On Thu, Jan 11, 2007 at 12:10:34AM -0300, Alvaro Herrera wrote:
> Jim C. Nasby wrote:
>
> > > Is the best way to do that usually to lower the scale factors? Is it
> > > ever a good approach to lower the scale factor to zero and just set the
> > > thresholds to a pure number of rows? (when setting
Jim C. Nasby wrote:
> > Is the best way to do that usually to lower the scale factors? Is it
> > ever a good approach to lower the scale factor to zero and just set the
> > thresholds to a pure number of rows? (when setting it for a specific
> > table)
>
> The problem is what happens if autovac
On Wed, Jan 10, 2007 at 04:48:42PM -0500, Jeremy Haile wrote:
> > BTW, that's the default values for analyze... the defaults for vacuum
> > are 2x that.
>
> Yeah - I was actually more concerned that tables would need to be
> analyzed more often than I was about vacuuming too often, so I used
> ana
On Wed, Jan 10, 2007 at 04:39:06PM -0500, Steven Flatt wrote:
> On 1/10/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >Except for the simplest partitioning cases, you'll be much better off
> >using a trigger on the parent table to direct inserts/updates/deletes to
> >the children. As a bonus, u
- Original Message -
From: "Jim C. Nasby" <[EMAIL PROTECTED]>
To: "Bob Dusek" <[EMAIL PROTECTED]>
Cc:
Sent: Wednesday, January 10, 2007 2:01 PM
Subject: Re: [PERFORM] performance implications of binary placement
Are you 100% certain that both builds are using all the same libraries?
> BTW, that's the default values for analyze... the defaults for vacuum
> are 2x that.
Yeah - I was actually more concerned that tables would need to be
analyzed more often than I was about vacuuming too often, so I used
analyze as the example. Since my app is inserting constantly throughout
the
On Wed, 2007-01-10 at 15:15, Jeremy Haile wrote:
> You can do list partitioning in MySQL:
> http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html
>
> My comment was not meant as a criticism of PostgreSQL's current state -
> I'm glad that it has partitioning. I'm simply wondering if there
On 1/10/07, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
Except for the simplest partitioning cases, you'll be much better off
using a trigger on the parent table to direct inserts/updates/deletes to
the children. As a bonus, using a trigger makes it a lot more realistic
to deal with an update moving
You can do list partitioning in MySQL:
http://dev.mysql.com/doc/refman/5.1/en/partitioning-list.html
My comment was not meant as a criticism of PostgreSQL's current state -
I'm glad that it has partitioning. I'm simply wondering if there are
any plans of adopting a more user-friendly syntax in th
On Wed, 2007-01-10 at 15:09, Jim C. Nasby wrote:
> On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> > This seems so much more intuitive and simpler than what is required to
> > set it up in PostgreSQL. Does PostgreSQL's approach to table
> > partitioning have any advantage over MySQ
Please cc the list so others can help and learn.
On Wed, Jan 10, 2007 at 03:43:00PM -0500, Jeremy Haile wrote:
> > I'd suggest trying autovacuum and see how it does (though you might want
> > to tune it to be more or less aggressive, and you'll probably want to
> > enable the cost delay).
>
> Wha
On Wed, Jan 10, 2007 at 03:28:00PM -0500, Jeremy Haile wrote:
> This seems so much more intuitive and simpler than what is required to
> set it up in PostgreSQL. Does PostgreSQL's approach to table
> partitioning have any advantage over MySQL? Is a "nicer" syntax planned
> for Postgres?
The focu
On 1/9/07, Simon Riggs <[EMAIL PROTECTED]> wrote:
If you are doing date range partitioning it should be fairly simple to
load data into the latest table directly. That was the way I originally
intended for it to be used. The rules approach isn't something I'd
recommend as a bulk loading option a
On Wed, 2007-01-10 at 14:15, Jim C. Nasby wrote:
> On Wed, Jan 03, 2007 at 12:24:24PM -0500, Jeremy Haile wrote:
> > I am sure that this has been discussed before, but I can't seem to find
> > any recent posts. (I am running PostgreSQL 8.2)
> >
> > I have always ran PostgreSQL on Linux in the past
On Tue, Jan 09, 2007 at 12:26:41PM -0500, Jeremy Haile wrote:
> I am developing an application that has very predictable database
> operations:
> -inserts several thousand rows into 3 tables every 5 minutes. (table
> contain around 10 million rows each)
> -truncates and rebuilds aggregate tab
Hey Jim -
Thanks for the feedback. The server has dual Xeons with HyperThreading
enabled - so perhaps I should try disabling it. How much performance
boost have you seen by disabling it? Of course, the bottleneck in my
case is more on the I/O or RAM side, not the CPU side.
Jeremy Haile
On W
On Wed, 10 Jan 2007, Jim C. Nasby wrote:
RAID1 for those of you who have been wondering if the BBU write back cache
mitigates the need for separate WAL (at least on this workload). Those
are
the fastest times for each config, but ext2 WAL was always faster than the
other two options. I didn't
I really wish that PostgreSQL supported a "nice" partitioning syntax
like MySQL has.
Here is an example:
CREATE TABLE tr (id INT, name VARCHAR(50), purchased DATE)
PARTITION BY RANGE( YEAR(purchased) ) (
PARTITION p0 VALUES LESS THAN (1990),
PARTITION p1 VALUES LESS THAN (1995),
PARTITIO
On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
Take a look at the set of partitioning functions I wrote shortly after
the 8.1 release:
http://www.studenter.hb.se/~arch/files/part_functions.sql
You could probably work something out using those functions (as-is, or
as inspirati
On Tue, Jan 09, 2007 at 09:10:51AM -0800, Jeff Frost wrote:
> On Tue, 9 Jan 2007, Jim C. Nasby wrote:
>
> >On Thu, Dec 28, 2006 at 02:15:31PM -0800, Jeff Frost wrote:
> >>When benchmarking various options for a new PG server at one of my
> >>clients,
> >>I tried ext2 and ext3 (data=writeback) for
On Mon, Jan 08, 2007 at 03:02:24PM -0500, Steven Flatt wrote:
> We use partitioned tables extensively and we have observed linear
> performance degradation on inserts as the number of rules on the master
> table grows (i.e. number of rules = number of partitions). We had to come
> up with a soluti
BTW, someone coming up with a set of functions to handle partitioning
for the general 'partition by time' case would make a GREAT project on
pgFoundry.
On Fri, Jan 05, 2007 at 12:47:08PM +0100, Mikael Carneholm wrote:
> Take a look at the set of partitioning functions I wrote shortly after
> the 8
On Fri, Jan 05, 2007 at 01:15:44PM -0500, Reid Thompson wrote:
> On Fri, 2007-01-05 at 04:10 +0100, Grega Bremec wrote:
> > he main reason I'm writing this mail though, is to suggest you take a
> > look
> > at Oryx, http://www.oryx.com/; They used to have this product called
> > Mailstore, which wa
On Wed, Jan 03, 2007 at 12:24:24PM -0500, Jeremy Haile wrote:
> I am sure that this has been discussed before, but I can't seem to find
> any recent posts. (I am running PostgreSQL 8.2)
>
> I have always ran PostgreSQL on Linux in the past, but the company I am
> currently working for uses Windows
On Wed, Jan 03, 2007 at 11:56:20AM -0500, Tom Lane wrote:
> Erik Jones <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> I could see this taking an unreasonable amount of time if you had a huge
> >> number of pg_class rows or a very long search_path --- is your database
> >> at all out of the or
Are you 100% certain that both builds are using all the same libraries?
And to be an apples-apples comparison, you really need to ensure that
the datadir is on the same filesystem in both cases (that's the first
thing I'd check).
Also, that pg_index... error sounds like the second build has been
c
I did create and drop an index at some point while looking at this
issue. But I definitely reran both of the queries (and explains) after
the index was dropped, so I don't understand why there would be a
difference between the inner and left query plans. (which were run
back-to-back more than onc
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> Another random idea - does PostgreSQL do any caching of query plans?
Only if the client specifies it, either by PREPARE or the equivalent
protocol-level message. I dunno what client software you were using,
but I think few if any would PREPARE behind y
Another random idea - does PostgreSQL do any caching of query plans?
even on the session level?
I ran these queries from the same Query window, so my idea is that maybe
the inner join plan was cached prior to an automatic analyze being run.
But I'm doubting PostgreSQL would do something like
I'm pretty sure it didn't analyze in between - autovac is turned off
and I ran the test multiple times before posting.
But since I can't reproduce it anymore, I can't be 100% sure. And it
certainly doesn't make sense that the estimate for the index scan would
change based on an unrelated join
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> I still don't understand why the inner join would be so different from
> the left join prior to the analyze.
Are you sure you hadn't analyzed in between? Or maybe autovac did it
for you? The reason for the plan change is the change from estimating
1 r
The table should have been analyzed, but to make sure I ran analyze on
the table before executing the explain analyze queries. Well - problem
solved. This time the inner join query runs quickly.
I still don't understand why the inner join would be so different from
the left join prior to the a
"Jeremy Haile" <[EMAIL PROTECTED]> writes:
> I have a query made by joining two subqueries where the outer query
> performing the join takes significantly longer to run than the two
> subqueries.
Please show EXPLAIN ANALYZE results, not just EXPLAIN.
Also, have you analyzed your tables recently?
For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the
equivalent
GROUP BY from index values alone.
Ok, that makes sense. Thanks for the help everybody!
If this table is large, perhaps you could denormalize and maintain a
summary table with date (using truncation) and cou
I have a query made by joining two subqueries where the outer query
performing the join takes significantly longer to run than the two
subqueries.
The first subquery runs in 600ms. The seconds subquery runs in 700ms.
But the outer query takes 240 seconds to run! Both of the two
subqueries onl
I wrote:
> ... What seems to be happening is that Informix is willing to
> flatten the sub-SELECT into an IN join even though the sub-SELECT is
> correlated to the outer query (that is, it contains outer references).
I did some googling this morning and found confirmation that recent
versions of I
* Jeremy Haile:
> Good advice on the partitioning idea. I may have to restructure some of
> my queries, since some of them query across the whole range - but it may
> be a much more performant solution. How is the performance when
> querying across a set of partitioned tables vs. querying on a s
37 matches
Mail list logo
