Kamalraj Singh Madhan wrote:
Hi,
I'am having major performance issues with post gre 7.3.1 db. Kindly suggest all the possible means by which i can optimize the performance of this database. If not all, some ideas (even if they are common) are also welcome. There is no optimisation done to th
Hi,
I'am having major performance issues with post gre 7.3.1 db. Kindly suggest all
the possible means by which i can optimize the performance of this database. If not
all, some ideas (even if they are common) are also welcome. There is no optimisation
done to the default configuration of
Folks,
I'm getting this plan on 7.2.4:
--
explain
select events.event_id, events.event_name, type_name,
COALESCE(cases.case_name || '(' || cases.docket || ')',
trial_groups.tgroup_name) as event_case,
jw_date_format(events.e
Damien Dougan <[EMAIL PROTECTED]> writes:
> Has anyone any ideas as to what could be causing the spiraling performance?
You really haven't provided any information that would allow anything
but guesses, but I'll guess anyway: poor plans for foreign key checks?
See nearby threads.
Greg Stark <[EMAIL PROTECTED]> writes:
> I'm still puzzled why the times on these are so different when the latter
> returns fewer records and both are doing sequential scans:
My best guess is that it's simply the per-tuple overhead of cycling
tuples through the two plan nodes. When you have no a
In fact the number of records seems to be almost irrelevant. A sequential scan
takes almost exactly the same amount of time up until a critical region (for
me around 10 records) at which point it starts going up very quickly.
It's almost as if it's doing some disk i/o, but I'm watching vmstat
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg is correct. int8 is a pass-by-reference datatype
Just to keep the conversation on track. the evidence from this particular post
seems to indicate that my theory was wrong and the overhead for count(*) is
_not_ a big time sink. It seems to be at most 1
Neil Conway <[EMAIL PROTECTED]> writes:
> Interesting. Is there a reason why int8 is pass-by-reference?
Pass-by-value types have to fit into Datum.
On a 64-bit machine (ie, one where pointers are 64-bits anyway) it would
make sense to convert int8 (and float8 too) into pass-by-value types.
If the
On Mon, 2003-10-27 at 13:52, Tom Lane wrote:
> Greg is correct. int8 is a pass-by-reference datatype and so every
> aggregate state-transition function cycle requires at least one palloc
> (to return the function result).
Interesting. Is there a reason why int8 is pass-by-reference? (ISTM that
pa
Neil Conway <[EMAIL PROTECTED]> writes:
> On Mon, 2003-10-27 at 12:56, Greg Stark wrote:
>> Neil Conway <[EMAIL PROTECTED]> writes:
>>> Uh, what? Why would an int8 need to be "dynamically allocated
>>> repeatedly"?
>>
>> Perhaps I'm wrong, I'm extrapolating from a comment Tom Lane made that
>> pro
On Mon, 2003-10-27 at 12:56, Greg Stark wrote:
> Neil Conway <[EMAIL PROTECTED]> writes:
> > Uh, what? Why would an int8 need to be "dynamically allocated
> > repeatedly"?
>
> Perhaps I'm wrong, I'm extrapolating from a comment Tom Lane made that
> profiling showed that the bulk of the cost in cou
Damien Dougan <[EMAIL PROTECTED]> writes:
> Our batch upload is performing a number of stored procedures to insert data on
> the database. Initially, this results in quite good performance, but rapidly
> spirals down to approximately 1 per second after some minutes.
It's fairly unlikely anyone
Neil Conway <[EMAIL PROTECTED]> writes:
> On Sun, 2003-10-26 at 22:49, Greg Stark wrote:
> > What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an
> > int8 to store its count so it's not limited to 4 billion records.
> > Unfortunately int8 is somewhat inefficient as it has to
Rob Nagler <[EMAIL PROTECTED]> writes:
> I didn't find ALTER SESSION for postgres (isn't that Oracle?), so I
> set sort_mem in the conf file to 512000, restarted postrgres. Reran
> the simpler query (no name) 3 times, and it was still 27 secs.
Sorry, I don't know how that bubbled up from the dep
On Mon, Oct 27, 2003 at 07:52:06AM -0500, Christopher Browne wrote:
> In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote:
> > I was answering an earlier response that suggested that maybe the actual
> > counting took time so it would take quite a bit longer when there are
> > more
On Mon, Oct 27, 2003 at 11:12:37AM -0500, Vivek Khera wrote:
> > "DM" == Dror Matalon <[EMAIL PROTECTED]> writes:
>
> DM> effective_cache_size = 25520 -- freebsd forumla: vfs.hibufspace / 8192
>
> DM> 1. While it seems to work correctly, I'm unclear on why this number is
> DM> correct. 2552
Rob Messer <[EMAIL PROTECTED]> writes:
> The problem comes in when we are selecting multiple field_name values
> in one query. The normal SQL syntax we have been using is like this:
> select field_name, option_tag from ds_rec_fld where recid = 3001 and
> field_name in ('Q3A1', 'Q3A9');
You'd hav
Greg Stark writes:
> I don't understand why you would expect overwriting to win here.
> What types of updates do you do on these tables?
These are statistics that we're adjusting. I think that's pretty
normal stuff. The DSS component is the avg() of these numbers on
particular groups. The gro
Greg Stark writes:
> Sorry I was unclear. By "usual case" I meant reading, as opposed to updates.
> The size of the on-disk representation turns out to be a major determinant in
> a lot of database applications, since the dominant resource is i/o bandwidth.
> Try doing a fresh import of a large tab
Hi All,
We've been experiencing extremely poor batch upload performance on our
Postgres 7.3 (and 7.3.4) database, and I've not been able to improve matters
significantly using any suggestions I've gleamed off the mailing list
archives ... so I was wondering if anyone with a bigger brain in this
> "DM" == Dror Matalon <[EMAIL PROTECTED]> writes:
DM> effective_cache_size = 25520 -- freebsd forumla: vfs.hibufspace / 8192
DM> 1. While it seems to work correctly, I'm unclear on why this number is
DM> correct. 25520*8 = 204160 or 200 Megs. On a machine with 2 Gigs it
DM> seems like the
On Fri, 2003-10-24 at 20:11, Allen Landsidel wrote:
> However, I do the same thing with the reindex, so I'll definitely be taking
> it out there, as that one does lock.. although I would think the worst this
> would do would be a making the index unavailable and forcing a seq scan..
> is that no
> "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes:
>> you need to bump some header file constant and rebuild the kernel. it
>> also increases the granularity of how the buffer cache is used, so I'm
>> not sure how it affects overall system. nothing like an experiment...
AL> So far I've f
> "AL" == Allen Landsidel <[EMAIL PROTECTED]> writes:
AL> However, I do the same thing with the reindex, so I'll definitely be
AL> taking it out there, as that one does lock.. although I would think
AL> the worst this would do would be a making the index unavailable and
AL> forcing a seq scan.
On Sun, 2003-10-26 at 22:49, Greg Stark wrote:
> What version of postgres is this?. In 7.4 (and maybe 7.3?) count() uses an
> int8 to store its count so it's not limited to 4 billion records.
> Unfortunately int8 is somewhat inefficient as it has to be dynamically
> allocated repeatedly.
Uh, what?
On Mon, 2003-10-27 at 10:15, Tarhon-Onu Victor wrote:
> select count(*) from items where channel <
> 5000; will never use any of the current indexes because none matches
> your WHERE clause (channel appears now only in multicolumn indexes).
No -- a multi-column index can be used to answer querie
http://fsbench.netnation.com/
Seems to answer a few of the questions about which might be the best
filesystem...
Chris
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's data
On Sun, 26 Oct 2003, Dror Matalon wrote:
> Here's the structure of the items table
[snip]
> pubdate | timestamp with time zone |
> Indexes:
> "item_channel_link" btree (channel, link)
> "item_created" btree (dtstamp)
> "item_signature" btree (signature)
> "items_channel_arti
Christopher Browne <[EMAIL PROTECTED]> writes:
> In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote:
> > I was answering an earlier response that suggested that maybe the actual
> > counting took time so it would take quite a bit longer when there are
> > more rows to count.
Tha
In the last exciting episode, [EMAIL PROTECTED] (Dror Matalon) wrote:
> I was answering an earlier response that suggested that maybe the actual
> counting took time so it would take quite a bit longer when there are
> more rows to count.
Well, if a "where clause" allows the system to use an index
On Sun, 26 Oct 2003 00:13:36 +0300, Hannu Krosing <[EMAIL PROTECTED]> wrote:
>UPDATE baz
> SET customer_id = '1234'
> WHERE baz_key IN (
>SELECT baz_key
> FROM baz innerbaz
> WHERE customer_id IS NULL
> and innerbaz.baz_key = baz.baz_key
> LIMIT 1000 );
AFAICS this is not
On Sat, 2003-10-25 at 13:49, Reece Hart wrote:
> Having to explicitly cast criterion is very non-intuitive. Moreover,
> it seems quite straightforward that PostgreSQL might incorporate casts
This is a well-known issue with the query optimizer -- search the
mailing list archives for lots more infor
On Mon, Oct 27, 2003 at 12:52:27PM +0530, Shridhar Daithankar wrote:
> Dror Matalon wrote:
>
> >On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
> >>Most of the time involves:
> >>
> >>a) Reading each page of the table, and
> >>b) Figuring out which records on those pages are st
Dror Matalon wrote:
On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
Most of the time involves:
a) Reading each page of the table, and
b) Figuring out which records on those pages are still "live."
The table has been VACUUM ANALYZED so that there are no "dead" records.
It's s
On Mon, Oct 27, 2003 at 01:04:49AM -0500, Christopher Browne wrote:
> [EMAIL PROTECTED] (Dror Matalon) wrote:
> > On Sun, Oct 26, 2003 at 10:49:29PM -0500, Greg Stark wrote:
> >> Dror Matalon <[EMAIL PROTECTED]> writes:
> >>
> >> > explain analyze select count(*) from items where channel < 5000;
>
35 matches
Mail list logo
