Its 8.4. On the column stats_target=-1 before I changed it. AutoVacuum
is set to on. I actually did a full analyze of the database and then
ran it again. So what am I missing?
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Friday, October 29, 2010 3:03 PM
To: Ozer
"Ozer, Pam" writes:
> Yes. The default statistics target was at 1000. So that would be what the
> column was using correct?
But you evidently didn't have stats. Perhaps you have autovacuum turned
off? What PG version is this anyway?
regards, tom lane
--
Sent via pg
Yes. The default statistics target was at 1000. So that would be what the
column was using correct?
-Original Message-
From: Josh Berkus [mailto:j...@agliodbs.com]
Sent: Friday, October 29, 2010 2:55 PM
To: Ozer, Pam
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow Quer
On 10/29/10 2:47 PM, Ozer, Pam wrote:
> I had just analyzed the dealergroupgeochache table. Wow. Thank you. That did
> the trick. Can you give me an explanation of the default_stats work? I don't
> think I completely understand what it means when you set it to 500 instead of
> 1000?
You're al
"Ozer, Pam" writes:
> I am not sure what you mean by reformulate the data representation. Do
> you mean do I have to join on all three columns?
No, I was wondering if you could change things so that you join on just
one column, instead of two that each tell part of the truth.
BTW, did you chec
I had just analyzed the dealergroupgeochache table. Wow. Thank you. That did
the trick. Can you give me an explanation of the default_stats work? I don't
think I completely understand what it means when you set it to 500 instead of
1000?
thanks
-Original Message-
From: pgsql-performan
I am not sure what you mean by reformulate the data representation. Do
you mean do I have to join on all three columns?
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Friday, October 29, 2010 2:18 PM
To: Ozer, Pam
Cc: pgsql-performance@postgresql.org
Subject: Re: [P
"Ozer, Pam" wrote:
> Is more information needed?
Table layouts of the tables involved (including indexes) would be
interesting. A description of the machine would be useful,
including OS, CPUs, RAM, and disk system.
I know you said you might have trouble changing the config, but some
of the
"Ozer, Pam" writes:
> Unfortunately I have not received a response on this question. Is more
> information needed? Does anyone have any ideas why the estimates may be
> bad? Or what I might be able to do to speed this up?
The most likely explanation for the bad rowcount estimates is that there
On Fri, 29 Oct 2010, James Mansion wrote:
Tom Lane wrote:
Uh, no, it is not. The difference is that we can update a byte in a
shared buffer, and know that it *isn't* getting written out before we
Well, I don't know where yu got the idea I was refering to that sort of thing
- its
the same as
On 10/29/10 1:54 PM, Ozer, Pam wrote:
> "-> Index Scan using dealergroupgeocache_i01 on
> dealergroupgeocache (cost=0.00..5719.56 rows=9055 width=10) (actual
> time=0.015..87.689 rows=163491 loops=1)"
This appears to be your problem here.
a) when was dealergroupgeocache last
Tom Lane wrote:
Uh, no, it is not. The difference is that we can update a byte in a
shared buffer, and know that it *isn't* getting written out before we
Well, I don't know where yu got the idea I was refering to that sort of
thing - its
the same as writing to a buffer before copying to the
Unfortunately I have not received a response on this question. Is more
information needed? Does anyone have any ideas why the estimates may be
bad? Or what I might be able to do to speed this up?
thanks
From: Ozer, Pam
Sent: Tuesday, October 26, 2010 4:27 PM
To: 'pgsql-performance@postg
My questions are: (1) Does the MVCC architecture introduce significant
delays between insert by a thread and visibility by other threads
As said by others, once commited it is immediately visible to all
(2) Are there any available benchmarks that can measure this delay?
Since you will not
On Fri, 29 Oct 2010, Robert Haas wrote:
On Thu, Oct 28, 2010 at 5:26 PM, Tom Lane wrote:
James Mansion writes:
Tom Lane wrote:
The other and probably worse problem is that there's no application
control over how soon changes to mmap'd pages get to disk. An msync
will flush them out, but th
[ please continue any further discussion in pgsql-bugs only ]
"Kevin Grittner" writes:
> Tom Lane wrote:
>> BTW this seems pretty far off-topic for pgsql-performance.
> It is once you understand what's happening. It was probably the 11+
> minutes for the mistyped query run, versus the 28 ms w
Excerpts from Greg Smith's message of jue oct 21 14:04:17 -0300 2010:
> What I would like to do is beef up the documentation with some concrete
> examples of how to figure out if your cache and associated write path
> are working reliably or not. It should be possible to include "does
> this h
Tom Lane wrote:
> BTW this seems pretty far off-topic for pgsql-performance.
It is once you understand what's happening. It was probably the 11+
minutes for the mistyped query run, versus the 28 ms without the
typo, that led them to this list.
I remembered this as an issued that has come up
On Wed, Oct 27, 2010 at 3:44 PM, Justin Pitts wrote:
>> Jason Pitts:
>> RE: changing default_statistics_target (or via ALTER TABLE SET STATS)
>> not taking effect until ANALYZE is performed.
>>
>> I did already know that, but it's probably good to put into this
>> thread. However, you'll note that
Jon Nelson writes:
> Initially, I mis-typed the query, and one of the columns specified in
> the query doesn't exist, however the query ran nonetheless.
> The actual query:
> select gid from t2, t3 where t2.name = t3.name and t3.scope = 'city'
> and t3.hierarchy = 'STANDARD' and t2.adiv = t3.adiv
Steve Wong wrote:
> (1) Does the MVCC architecture introduce significant delays
> between insert by a thread and visibility by other threads (I am
> unclear about how multiple versions are "collapsed" or reconciled,
> as well as how different query threads are seeing which version)?
As soon as
On Oct 25, 2010, at 2:46 PM, Steve Wong wrote:
> Hi experts,
>
> I have a (near) real-time application in which inserts into the database
> needs
> to be visible to queries from other threads with minimal delay. The inserts
> are
> triggered by real-time events and are therefore asynchronous
> Jason Pitts:
> RE: changing default_statistics_target (or via ALTER TABLE SET STATS)
> not taking effect until ANALYZE is performed.
>
> I did already know that, but it's probably good to put into this
> thread. However, you'll note that this is a temporary table created at
> the beginning of a t
If you alter the default_statistics_target or any of the specific
statistics targets ( via ALTER TABLE SET STATISTICS ) , the change
will not have an effect until an analyze is performed.
This is implied by
http://www.postgresql.org/docs/9.0/static/planner-stats.html and
http://www.postgresql.org/
Dear All,
Thanks for your inputs on the insert performance part.
Any suggestion on storage requirement?
VACUUM is certainly not an option, because this is something related to
maintenance AFTER insertion.
I am talking about the plain storage requirement w.r. to Oracle, which I
observed is twice
Thank you for all of the responses. This was really helpful.
Damon
On Sat, Oct 16, 2010 at 12:54 PM, Merlin Moncure wrote:
> On Fri, Oct 15, 2010 at 10:31 PM, Tom Lane wrote:
> > Tatsuo Ishii writes:
> >> So can I say "if a function is marked IMMUTABLE, then it should never
> >> modify databa
Hi experts,
I have a (near) real-time application in which inserts into the database needs
to be visible to queries from other threads with minimal delay. The inserts are
triggered by real-time events and are therefore asynchronous (i.e. many
performance tips I read related to batch inserts or
Hello,
What is the general view of performance CPU's nowadays when it comes to
PostgreSQL performance? Which CPU is the better choice, in regards to
RAM access-times, stream speed, cache synchronization etc. Which is the
better CPU given the limitation of using AMD64 (x86-64)?
We're getting
I've been having trouble with a query.
The query is a cross join between two tables.
Initially, I mis-typed the query, and one of the columns specified in
the query doesn't exist, however the query ran nonetheless.
The actual query:
select gid from t2, t3 where t2.name = t3.name and t3.scope = 'ci
On Fri, Oct 29, 2010 at 11:56 AM, Aidan Van Dyk wrote:
> 1) The pages you write to must be in the page cache, or your memcpy is
> going to fault them in. With a plain write, you don't need the
> over-written page in the cache.
I seem to remember a time many years ago when I got bitten by this
pr
> -Original Message-
> From: Ben [mailto:midfi...@gmail.com]
> Sent: Friday, October 29, 2010 12:16 PM
> To: Igor Neyman
> Cc: pgsql-performance@postgresql.org
> Subject: Re: partitioning question 1
>
> On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:
>
> >> is my intuition completely
On Oct 29, 2010, at 7:38 AM, Igor Neyman wrote:
>> is my intuition completely off on this?
>>
>> best regards, ben
>>
>
> If your SELECT retrieves substantial amount of records, table scan could
> be more efficient than index access.
>
> Now, if while retrieving large amount of records "WHERE
Robert Haas writes:
> On Thu, Oct 28, 2010 at 5:26 PM, Tom Lane wrote:
>> It's true that we don't know whether write() causes an immediate or
>> delayed disk write, but we generally don't care that much. What we do
>> care about is being able to ensure that a WAL write happens before the
>> data
On Fri, Oct 29, 2010 at 11:43 AM, Robert Haas wrote:
> Well, we COULD keep the data in shared buffers, and then copy it into
> an mmap()'d region rather than calling write(), but I'm not sure
> there's any advantage to it. Managing address space mappings is a
> pain in the butt.
I could see thi
On Thu, Oct 28, 2010 at 5:26 PM, Tom Lane wrote:
> James Mansion writes:
>> Tom Lane wrote:
>>> The other and probably worse problem is that there's no application
>>> control over how soon changes to mmap'd pages get to disk. An msync
>>> will flush them out, but the kernel is free to write dir
> -Original Message-
> From: Ben [mailto:midfi...@gmail.com]
> Sent: Thursday, October 28, 2010 12:37 PM
> To: pgsql-performance@postgresql.org
> Subject: partitioning question 1
>
> hello --
>
> my last email was apparently too long to respond to so i'll
> split it up into shorter piec
36 matches
Mail list logo
