2010/11/11 Robert Haas robertmh...@gmail.com
But thinking over what you've written here, I'm reminded of something
Peter said years ago, also about the optimizer. He was discussed the
ratio of the estimated cost to the actual cost and made an off-hand
remark that efforts had been made over
But I doubt your answer. I think the essence of the problem is when the
planner selects 'Bitmap Index Scan' and how the planner computes the cost
of 'Bitmap Index Scan'.
The essence of the problem obviously is a bad estimate of the cost. The
planner considers the two plans, computes the costs
Hello,
in the last years, we have successfully manage to cope with our data
growth
using partitioning and splitting large aggregation tasks on multiple
threads.
The partitioning is done logically by our applicationn server, thus
avoiding trigger overhead.
There are a few places in our data flow
On Wed, Nov 10, 2010 at 10:47:21PM -0500, Robert Haas wrote:
On Wed, Nov 10, 2010 at 6:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Robert Haas robertmh...@gmail.com wrote:
Unfortunately, to know how much data we're going to grovel
through,
On Thu, Nov 11, 2010 at 02:41:12PM +0100, Marc Mamin wrote:
Hello,
in the last years, we have successfully manage to cope with our data
growth
using partitioning and splitting large aggregation tasks on multiple
threads.
The partitioning is done logically by our applicationn server, thus
Kenneth Marshall wrote:
I agree with the goal of avoiding the need for a GUC. This needs to
be as automatic as possible. One idea I had had was computing a value
for the amount of cache data in the system by keeping a sum or a
weighted sum of the table usage in the system. Smaller tables and
On Thu, Nov 11, 2010 at 09:15:58AM -0500, Mladen Gogala wrote:
Kenneth Marshall wrote:
I agree with the goal of avoiding the need for a GUC. This needs to
be as automatic as possible. One idea I had had was computing a value
for the amount of cache data in the system by keeping a sum or a
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
create a definitive bias toward one type of the execution plan.
We're talking about trying to support the exact opposite. This all
started because a database which was tuned for good response time
for relatively small queries against a hot
--- On Thu, 11/11/10, Mladen Gogala mladen.gog...@vmsinfo.com wrote:
From: Mladen Gogala mladen.gog...@vmsinfo.com
Subject: Re: [PERFORM] anti-join chosen even when slower than old plan
To: Kenneth Marshall k...@rice.edu
Cc: Robert Haas robertmh...@gmail.com, Tom Lane t...@sss.pgh.pa.us,
Kevin Grittner wrote:
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
create a definitive bias toward one type of the execution plan.
We're talking about trying to support the exact opposite.
I understand this, that is precisely the reason for my intervention into
the discussion
Mladen Gogala mladen.gog...@vmsinfo.com writes:
Again, having an optimizer which will choose the plan completely
accurately is, at least in my opinion, less important than having a
possibility of manual control, the aforementioned knobs and buttons
and produce the same plan for the same
On 11/11/10 9:13 AM, Mladen Gogala wrote:
Kevin Grittner wrote:
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
create a definitive bias toward one type of the execution plan.
We're talking about trying to support the exact opposite.
I understand this, that is precisely the reason for my
On Thu, Nov 11, 2010 at 10:00 AM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Mladen Gogala mladen.gog...@vmsinfo.com wrote:
create a definitive bias toward one type of the execution plan.
We're talking about trying to support the exact opposite. This all
started because a database
Tom Lane wrote:
More knobs and buttons is the Oracle way,
True. Very true.
and the end result of that
process is that you have something as hard to use as Oracle.
Also, you end up with something which is extremely reliable and
adjustable to variety of conditions.
That's
generally not
Robert Haas robertmh...@gmail.com writes:
Let's back up a moment and talk about what the overall goal is, here.
Ideally, we would like PostgreSQL to have excellent performance at all
times, under all circumstances, with minimal tuning. Therefore, we do
NOT want to add variables that will, by
On Thu, Nov 11, 2010 at 06:41, Marc Mamin m.ma...@intershop.de wrote:
There are a few places in our data flow where we have to wait for index
creation before being able to distribute the process on multiple threads
again.
Would CREATE INDEX CONCURRENTLY help here?
--
Sent via
I wrote:
I do think that something based around a settable-per-table caching
percentage might be a reasonable way to proceed.
BTW ... on reflection it seems that this would *not* solve the use-case
Kevin described at the start of this thread. What he's got AIUI is some
large tables whose
On Thu, Nov 11, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
Let's back up a moment and talk about what the overall goal is, here.
Ideally, we would like PostgreSQL to have excellent performance at all
times, under all circumstances, with
On Thu, Nov 11, 2010 at 1:58 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I wrote:
I do think that something based around a settable-per-table caching
percentage might be a reasonable way to proceed.
BTW ... on reflection it seems that this would *not* solve the use-case
Kevin described at the
Tom Lane t...@sss.pgh.pa.us wrote:
BTW ... on reflection it seems that this would *not* solve the
use-case Kevin described at the start of this thread. What he's
got AIUI is some large tables whose recent entries are well-
cached, and a lot of queries that tend to hit that well-cached
Robert Haas robertmh...@gmail.com writes:
On Thu, Nov 11, 2010 at 1:23 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I do think that something based around a settable-per-table caching
percentage might be a reasonable way to proceed. But the devil is in
the details, and we don't have those yet.
I
I wrote:
Besides the fully-scanned object size relative to relation size
costing adjustment idea,
s/relation size/effective cache size/
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Besides the fully-scanned object size relative to relation size
costing adjustment idea, the only one which seemed to be likely to
be useful for this sort of issue was the costing factors by user
ID idea -- the interactive queries hitting the
Tom Lane t...@sss.pgh.pa.us wrote:
I've tried to avoid having the planner need to know the total size
of the database cluster, but it's kind of hard to avoid that if
you want to model this honestly.
Agreed. Perhaps the cost could start escalating when the pages to
access hit
Tom Lane t...@sss.pgh.pa.us wrote:
Erm ... you can in fact do ALTER USER SET random_page_cost
today.
Ouch. I'm embarrassed to have missed that. I'll do that instead of
adding those settings to the scripts, then.
Thanks for pointing that out.
-Kevin
--
Sent via pgsql-performance
No, CONCURRENTLY is to improve table availability during index creation, but it
degrades the performances.
best regards,
Marc Mamin
-Original Message-
From: Alex Hunsaker [mailto:bada...@gmail.com]
Sent: Donnerstag, 11. November 2010 19:55
To: Marc Mamin
Cc:
On Thursday 11 November 2010 19:58:49 Tom Lane wrote:
I wrote:
I do think that something based around a settable-per-table caching
percentage might be a reasonable way to proceed.
BTW ... on reflection it seems that this would *not* solve the use-case
Kevin described at the start of this
On Thu, Nov 11, 2010 at 1:41 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Besides the fully-scanned object size relative to relation size
costing adjustment idea, the only one which seemed to be likely to
be useful for this sort of issue was the
On Thu, Nov 11, 2010 at 2:35 PM, Tom Lane t...@sss.pgh.pa.us wrote:
Robert Haas robertmh...@gmail.com writes:
Yeah. For Kevin's case, it seems like we want the caching percentage
to vary not so much based on which table we're hitting at the moment
but on how much of it we're actually reading.
Original message
Date: Thu, 11 Nov 2010 15:29:40 -0500
From: pgsql-performance-ow...@postgresql.org (on behalf of Robert Haas
robertmh...@gmail.com)
Subject: Re: [PERFORM] anti-join chosen even when slower than old plan
To: Tom Lane t...@sss.pgh.pa.us
Cc: Kevin Grittner
On Thu, Nov 11, 2010 at 03:56:25PM -0500, gnuo...@rcn.com wrote:
On a thread some time ago, on a similar subject, I opined that I missed the
ability to assign tables to tablespaces and buffers to tablespaces, thus
having the ability to isolate needed tables (perhaps a One True Lookup Table,
dear pgers --
consider the following toy example (using pg 8.4.3) :
create temporary table foo (
ts timestamp not null,
id integer not null,
val double precision not null,
primary key (ts, id)
);
i might want to return the vals, minus the averages at each timestamp. the
obvious
Ben midfi...@gmail.com writes:
us_quotedb=# explain select ts, id, val - aval from foo join (select ts,
avg(val) as aval from foo group by ts) as a using (ts) where ts
'2010-11-11' and ts '2010-11-13';
appreciate the instant response.
Well, arguably it's not doing the right thing either way --- you'd sort
of like the inequalities to get pushed down into both of the join
inputs, not just one of them. PG doesn't make that deduction though;
it can make such inferences for equalities, but
On Fri, Nov 5, 2010 at 7:26 PM, Jon Nelson jnelson+pg...@jamponi.net wrote:
I have a postgres 8.4.5 instance on CentOS 5 (x86_64) which appears to
go crazy with the amount of memory it consumes.
When I run the query below, in a matter of a few seconds memory
balloons to 5.3G (virtual), 4.6G
Hello
look on EXPLAIN ANALYZE command. Probably your statistic are out, and
then planner can be confused. EXPLAIN ANALYZE statement show it.
Regards
Pavel Stehule
2010/11/12 Jon Nelson jnelson+pg...@jamponi.net:
On Fri, Nov 5, 2010 at 7:26 PM, Jon Nelson jnelson+pg...@jamponi.net wrote:
I
On Thu, Nov 11, 2010 at 10:26 PM, Pavel Stehule pavel.steh...@gmail.com wrote:
Hello
look on EXPLAIN ANALYZE command. Probably your statistic are out, and
then planner can be confused. EXPLAIN ANALYZE statement show it.
As I noted earlier, I did set statistics to 1000 an re-ran vacuum
analyze
2010/11/12 Jon Nelson jnelson+pg...@jamponi.net:
On Thu, Nov 11, 2010 at 10:26 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hello
look on EXPLAIN ANALYZE command. Probably your statistic are out, and
then planner can be confused. EXPLAIN ANALYZE statement show it.
As I noted earlier,
38 matches
Mail list logo
