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
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 still live.
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
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 what the
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 to
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.
That was my
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_article btree
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
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 queries
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?
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 found a whole
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 not
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 number
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
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. 25520*8 = 204160 or
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 rows
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 depths
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 be
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 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
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
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
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 10%
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
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.
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,
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
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
28 matches
Mail list logo
